When designing your reports in Power BI Desktop you properly spent a lot of time making sure your visualizations is aligned and at least for some of them making sure they have the same size.
So far, we only have the align feature in the Power BI Desktop
To change the size of the visualizations we must use the General properties under Format to resize the elements
But what if you want to resize more than one element at a time – If you select more than one you get the size of the first selection in the general tab
Now here is the trick – modify the width and Height with 1 each
And then back again
And your visualizations have the same size.
OBS – This only works when you select the same type of visualizations – if select different types you won’t be able to see General under Format.
Hope this can help you too –
Just wanted to share a quick tip I just stumbled upon when designing a data model in Excel Power Pivot.
For creating a measure in Power Pivot, I place the cursor under the column and then I click the AutoSum button on the Home tab –
And then I get the formula created quick and I can modify the name.
But did you know that if you multiselect several cells in the Calculation Area and click the AutoSum – you get formulas for all the columns you have selected
Hope you find this useful as well …
Many blogposts has been written about how you can create a generic date table in your Power Pivot / Power BI datamodels.
Here is a few examples
But my challenge with these has always been that I had to specify from date and to date either in a worksheet, text file or directly in the Query Editor.
Normally your fact table actually contains the max and min date that you actually want your date table to contain – so in the last model I created I decided to see if I could make the from and to date dynamic based on the max and min date in the fact table.
By using a bit of M magic functions it turned out to be very easy.
In my example I load the FactInternetSales table from the AdventureWorks database
Now as we can see in the query editor we have a column containing the Order date and the min and max of that column should be used to generate the table of dates.
Convert the OrderDate to Date instead of DateTime
Then add a blank query via the New Source button
In the formula bar you enter the following formula
And you get a list of dates – 🙂
Now we can add columns with the date information we want in our date tableby using the add Column Date – From Date & Time
The date table now depends on the Query FactInternetSales – and with the September update of the Power BI Desktop we can see that via the Query dependencies window
Now Power Query apparently knows that it has to update FactInternetSales before it updates the Date table, so when our facttable is updated the Dates get updates afterwards.
You could by modifying the expression to get the list of dates calculate the first day of the min year and last date of the max year etc. – all this depends on how complete you want the datetable.
Power Query is AWESOME J
When you design reports in PowerBI using the online designer and not the Power BI Desktop designer you will notice that you cannot insert a picture via the interface.
With the development speed of PowerBI we will properly get it soon – but until that you might be find this workaround to do it via the datamodel interesting.
Let us switch to the Power BI Desktop designer and open a new report to create a datamodel we can publish to PowerBI.
Inspired by Chris Webb on how to create a table using M – (link) I have created a list of pictures using the #table statement
And this gives me a table with a number of Power BI celebrities J and a link to their twitter picture.
Now set the column ImageURL Category to Image URL
Now I can save the Desktop file and publish this to Power BI.
And now you can use the table or matrix and insert the ImageURL field on to your canvas.
Remember to use the Visual level filters to pick the image you want.
Here is a list of short cut keys that I almost use every time I work in Excel.
The short cut keys works in the English version of Excel – localized version may use other keys.
CTRL + ‘ – copies the content of the cell above and enter edit state of the cell – very handy when creating almost the same formula as the cell above
F4 – when you are editing a cell reference in the formula bar the key cycles through the absolute and relative for column ref and row ref
CTRL+ _ (underscore) – removes any border around the selected cells
CTRL + ENTER – when a range or multiple cells is selected and you press CTRL + ENTER when you input values or formulas the input will be inserted in your entire selection
CTRL + F1 – Hide the Ribbon
CTRL + 1 – Open the format cells dialog
Hope these can help you too – if you have other favourites please let me know in the comments below.
The Danish Central Business Register -https://datacvr.virk.dk/data/?language=en-gb& contains a lot of data including the possibility to find the annual account for all companies in Denmark.
These are available in two formats – pdf and XBRL format – so for instance for Microsoft Denmark ApS we can find the latest Annual Accounts (financial statement) via this search
As you can see, we can get the document via the link XBRL and get the annual account in XML format like this
You can read more about the XBRL format here – https://www.xbrl.org/
So knowing that we can search for companies using their CVR number and find the latest account let’s try to retrieve the data using Power BI Desktop.
Find the company data
Open the Power BI Desktop and choose get data – and the source is Web
This will give you the HTML document
But we want to find the links to the XBRL accounts so we actually don’t want it opened as an HTML document – so click Edit and in the steps pane click the source to change it to open the document as a Text file
And delete the navigation step after Source.
Now we can find all the rows where XBRL is
This gives us the three rows with information about the link to the accounts in XBRL format
Then trim the column and Split the value by ” – gives us a column 1.8 containing the link to the file.
Now we can delete unwanted columns, add an index and filter by 0 in the index column to get the latest account
Now we have a table with a link to the latest XBRL account for a given company.
Retrieve the account information
To get specific data in the accounts we can use this XBRL link to do a new query retrieving the data
So click on Home tab and New source and select Web again
This gives us the navigator to the XML document
If you are interested in specific elements you can tick those – but click for instance Context and click OK and via the Query Settings click source
Click on table in the node – http://xbrl.dcca.dk/fsa
And you get all the account lines
Then expand the Table column and choose all elements
Now in order to get the current years data you can filter by the contextRef c1
Now after some filtering and removing of duplicate rows – we end up with the lines for the profit and loss as seen here
The values are all positive so in order to handle this – we can pivot the data and do multiplication with -1 and unpivot the columns again
Then we can load this data and visualize the Profit and loss in a waterfall chart
By default the waterfall sorts the data by A-Z which isn’t exactly what we want – luckily there is a workaround for this
Choose Edit Queries and add an Index column to the Context query.
Load the data and select the Attribute in the field pane – and in the Modeling select Sort by Column and choose to sort by Index
And after some formatting etc. we can illustrate the Profit & Loss for Microsoft Denmark like this
Now we can wait a few month to the next Account is done and just click refresh.
Using functions and/or parameters you can make this highly flexible – if this post gets more than 10 likes J – I promise to show you how that can be done.
Hope you liked this – if you want to play around with you can download the pbix here.
While we are waiting for the alignment feature of the elements in the Power BI Designer – you can vote for it here, – https://ideas.powerbi.com/forums/265200-power-bi/suggestions/6932400-add-alignment-positioning-and-sizing-controls-for – I have found a workaround that you might find useful.
If you press CTRL while selecting two elements on the page the placeholders for the elements is visible on screen.
After selection, you can change the size of the element you want to resize and use the handles of the other element to align with.
OBS – When you have multi-selected elements you can also move them as a group.