Wednesday, September 28, 2016

How to connect to an Excel file on onedrive with Power BI desktop

In the Power BI web version is it possible to open a file on a OneDrive location. In the desktop version this is not possible in the standard way.

On a non-standard way it is possible to connect Power BI desktop to a file on a One drive location.

Here are the steps to follow:

Step 1 - Go to your OneDrive online environment and login


Step 2 - Open the desired file in Excel


Step 3 - Go to File --> Info


Step 4 - Select the link above the Protect Workbook button. Select Copy link to clipboard.


Step 5 - Use this link in Power BI desktop


Step 6 - Paste link


Step 7 - Remove the ?web=1 part at the end of the link, click OK. Login with your OneDrive/o365 account.

Tuesday, September 27, 2016

How to sort month names in Power BI

When names of months used in Power BI you'll be able to sort this A-Z or Z-A. Both do not give the correct order:

A-Z


or Z-A


This problem can be solved in some simple steps. 

Step 1 - Add a month number column to your dataset, like this:


Step 2 - Click month column in data tab of Power BI, like this:


Step 3 - Go to the modeling tab and click Sort By Column, like this:


Step 4 - Click MonthNr in the drop down


Now your report looks like this