Exporting Data from Power BI Desktop to Excel and CSV – Part 2: Importing Power BI Data Directly to Excel

Update 2019 April: If you’re interested in exporting the data model from either Power BI Desktop or Power BI Service to CSV or SQL Server check this out. The method explained here is only applicable for Power BI Premium or Embedded capacities with XMLA endpoints connectivity.

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

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, 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

Now that we have the port number it is easy to connect to the model.

Open Excel

Click “From Other Sources” from “Data” tab from the ribbon

Click “From Analysis Services”

Enter the server name as “localhost:56770” where “56770” is my Power BI Desktop local port number

Click “Next”

Click Next one more time

Now we can rename connection file to make it more readable. We need to get back to this file in the next steps

Click “Browse” to save the connection file in a desired folder

Click “Finish”

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:

EVALUATE ‘FactFinance’

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”

Voila!

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

All done!

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.

Like this: Like Loading...