Someone asked me a question yesterday about exporting data from the Power BI Service into Excel. There are a few options to do this however they all have their problems (these problems are not covered in great detail in this post).
- Power BI has an inbuilt export data feature (there is an export limit of 30k rows for a free account and 150k rows for a Pro account. Plus the connection cannot be refreshed).
- Analyze in Excel (can only view the data in Excel via a pivot table, not a table of records).
- Download the Power BI Desktop file to your PC and use DAX Studio (you have to download the entire model just to get the records you want, besides it may not even work at all).
I started to think that I may be able to connect directly to Power BI Service using DAX Studio and then use the techniques I cover here to extract the records. I tried this but couldn’t get it to work (please let me know if you find a way to do this). I started searching and found this old article I wrote in 2015 that uses a technique I learnt from Marco Russo. l have used a similar approach to create a direct query of a Power BI Data Model and extract the records directly into a Table in Excel.
Edit: 11 Jan 2017 – see the bottom of this post to see how Chris Webb recommends to do this task – it is much easier :-). But read on for some interesting learning.
Create a Link to PowerBI.com
The first thing I did was to select my dataset from the Power BI Service, clicked the Ellipsis (1 below) and then Analyze in Excel (2 below).
This downloads an ODC file to the downloads folder. Mine was called Adventure Works.odc
Note: there have been many updates to the Analyze in Excel connectors since its launch last year. If you haven’t updated the Analyze in Excel connectors for a while you would be well advised to do that first before attempting the rest of the procedure that follows. This can be done by logging into Power BI, clicking Downloads (shown as 1 below) and then downloading and installing the connector software (2 below).
Edit the ODC File to Make it a Query
I then edited the ODC file using Notepad. It looks like this originally
It is a bit hard to read the file as is, so I cut and paste the contents into an HTML editor to improve the readability, (as shown below) then pasted the formatted text back into Notepad.
As you can see above, the default command is “Cube” and the default text is “Model”. The trick is to change the command to be “Query” and replace the text to be any valid DAX Query.
I changed lines 19 and 20 below as follows:
<odc:CommandType>Query</odc:CommandType> <odc:CommandText>Evaluate Sales</odc:CommandText>
The Command Text I used above is one of the most simple DAX Queries you can write. All queries must start with the EVALUATE statement followed by any valid DAX code that returns a table of data. The simplest way to do that is just specify an existing table (Sales in this case). If you are doing this, you can use any table name in your data model.
I then saved and closed the ODC file.
Open the ODC in Excel
To open the ODC file in Excel, I then simply double clicked on the file. This opened Excel and gave me the following security warning
I clicked enable, and there appeared the entire Sales table from my Power BI data model directly in a table in Excel.
Now that the connection has been established, the file can be saved and refreshed as needed in the future. To refresh, simply open the file, right click on the table and click “refresh”.
Change the Query
So far this is pretty boring albeit maybe useful for some people. Much more interesting is to be able to extract a subset of the data to Excel. To do this you need to learn a bit of the DAX Query Language. I am not going to go into the DAX Query Language in detail in this post, but below I show how to edit the current query and change it to a new query – in this case a query that will return the Total Sales by Invoice Number.
To edit the existing query, I simply right clicked anywhere in the table (1 below), then selected Table (2 below), then Edit Query (3 below).
This brings up a dialog that can be edited as needed.
For my demo, I replaced the command text with my new DAX Query as follows
After clicking OK, this query then returned a table of all invoice numbers and the total value of sales for those orders. Interestingly the number formatting for Total Sales did not flow through to Excel as I expected it to.
Chris Webb’s Trick
Edit 11 Jan 2017. Chris Webb tweeted an alternate approach that is faster to execute. Simply create any pivot table using Analyze in Excel, drill through to the underlying table and then edit the query as before.
I may come back and blog about DAX as a Query language at another time. Until then if you would like to learn what is possible, I recommend this video from Alberto Ferrari (although it is a bit advanced unless you have some solid understanding of tables and databases).