In my previous post I explained how to copy and paste data from Power BI Desktop into Excel or CSV. I also explained how easy you can export Power BI Desktop data to CSV using DAX Studio. As I promised, in this post I show you how to import Power BI Desktop data to Excel directly. In this method you don’t need to use any third-party software and the performance is much better than the previous methods.
Note: The method I explain in this post is tested in Excel 2016 only. But, it should work for Excel 2013.
Importing Power BI Desktop Directly to Excel Directly
In one of my previous posts I explained how to connect to a Power BI Desktop from Excel. To import Power BI Desktop data to Excel we have to do the same thing. I explain the way to connect to a Power BI Desktop model directly from Excel again then I show you how to use this method to import Power BI Desktop data.
Finding Power BI Desktop local port number from Power BI Desktop temp directory
We can find Power BI Desktop local port number in number of ways explained here. So in this post I don’t go through all methods.
Whenever we run Power BI Desktop, it opens a random port number. The port number is independent of the model so it doesn’t really matter if we haven’t connected to any data sources or even if we haven’t open any saved Power BI Desktop (*.PBIX) files. That port number is stored in a text file named “msmdsrv.port.txt”. So the only thing we need is to do is to browse the temp directory of Power BI Desktop and open the “msmdsrv.port.txt” text file. You can find Power BI Desktop temp folder here:
%LocalAppData%\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces
There should be an “AnalysisServicesWorkspaceXXX” folder which XXX is a random number. Open that folder then open “Data” and Find “msmdsrv.port.txt”. Open the file to see Power BI Desktop local port number.
Connecting to Power BI Desktop Model from Excel
- Click “Next”
- You can cancel “Import Data” as we don’t need it
We successfully connected to Power BI Desktop and we also created a connection file. Let’s move forward.
Modifying ODC connection file
In previous steps we created a connection file named “Import Power BI Desktop Data to Excel.odc”. You should find this file in the folder you selected earlier. If you haven’t selected any particular folder, the default folder is:
%UserProfile%\Documents\My Data Sources\
- Find the odc file
- Right click and select
- When you open odc file in Notepad, find“<odc:CommandType>” and change the command type from “Cube” to “Default”
- The next line is command text. Here is the trick. You have to replace “Model” with a DAX query. So if you want to import “FactFinance” from “Adventure Works” you can write the following DAX query:
Now we force Excel to run the DAX query on top of our Power BI Desktop model.
- Save the file and close it
We’re almost there.
- Double click the odc file to run it in Excel
- Click “Enable” when you get “Microsoft Excel Security Notice”
We imported “FactFinance” data from Power BI Desktop to Excel.
How to import other tables’ data to Excel?
It’s easy to import other tables’ data to Excel. Just follow the steps below:
- Make a copy of the same odc file
- Edit the DAX query
- Create a new sheet in Excel
- Click “Existing Connections” from “Data” tab from the ribbon
- Click “Browse for More”
- Find the new copy of odc file you created earlier and click “Open”
- Click OK
Please note that if you have millions of rows of data in your Power BI Desktop then you’ll be able to load 1,048,576 rows which is maximum number of rows limitation on Excel.
So it seems in many cases importing Power BI Desktop data to Excel won’t be an option just because of maximum row number limitation in Excel.
In the next article I explain how to export Power BI Desktop data to a SQL Server database.
So stay tuned.