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

 

AAEAAQAAAAAAAAz4AAAAJGQ5ZTk4ZGUxLTI4YjQtNDc4ZC05NTQ4LWRjNDk3OTBlYTE5OQ

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.

image

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
  • image

    • Click “Next”

    image

  • Click Next one more time
  • image

    • 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”

    image

    • You can cancel “Import Data” as we don’t need it

    image

    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

    image

    • 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

    image

    We’re almost there.

    • Double click the odc file to run it in Excel
    • Click “Enable” when you get “Microsoft Excel Security Notice”

    image

    image

    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

    image

    • Create a new sheet in Excel
    • Click “Existing Connections” from “Data” tab from the ribbon

    image

    • Click “Browse for More”

    image

    • Find the new copy of odc file you created earlier and click “Open”

    image

    • Click OK

    image

    image

    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’ll explain how to export Power BI Desktop data to a SQL Server database.

    So stay tuned. Smile

    Leave a Reply