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

AAEAAQAAAAAAAAz4AAAAJGQ5ZTk4ZGUxLTI4YjQtNDc4ZC05NTQ4LWRjNDk3OTBlYTE5OQ

Update 2019 April: If you’re interested in exporting the data model from Power BI Desktop or Power BI Service to CSV or SQL Server check this out.

Note: The method explained in the above post involves Power BI Premium or Embedded capacities with XMLA endpoints connectivity at the time of writing.

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.

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

So stay tuned. Smile

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

  1. Hi,

    this is great!
    But is there a way to remove the “TABLE[]” from Columns Names?
    Thanks,

    JM

    1. Hi Jean and welcome to BI Insight.
      In this method we are OLEDB MSOLAP data provider. If you connect to your Power BI Desktop model using localhost with corresponding local port number in SSMS and run “EVALUATE ‘DimDate’ ” you’ll get exact same results.
      As far as I concern, in this method there is no easy way to get rid of TABLE[] in column names.

      Hope that helps.
      Cheers

  2. Thanks a lot!
    Great method!
    I can’t to connect afer I closed the Power BI file.
    What does it mean?
    I need to do a new odc file every time after opening the PBI file?

    1. Hi Alex,

      Welcome to BI Insight.
      You’re right, when you close the file, your connectrion is lost.
      Put it that way, imagine that you’re connected to an instance of SQL Server Analysis Services Tabular Model.
      What happens if you shut the service down? You lose your connection rights?
      Now suppose your instance of SQL Server Analysis Services Tabular Model runs over random port numbers.
      To be able to re-connect, you need to find the new port number.
      This is indeed the case when you close the Power BI Desktop.
      You need to find the port number and the database name, then you need to modify the odc file in Notepad and save it.
      Remember, not to close the file until you finished your work, otherwise you have to revisit the steps above.
      To find the Power BI Desktop port number check this out: https://biinsight.com/four-different-ways-to-find-your-power-bi-desktop-local-port-number/

      Hope that helps.

      Cheers

    1. Hi there,
      I’m not too sure what you mean by renaming Excel file.
      Can you explain the scenario a bit more?
      Are you trying to export a visual data from Power BI Service? If that’s the case then the Excel file name would be the same as the visual name.
      If you’re trying to export visual data from Power BI Desktop to CSV, then that’s the case for now. You have to manually rename the “data.csv” file.
      Cheers.

  3. Following error messages are displayed while applying the above steps. Please note that to locate port number, I had two folders by the name of analysis services workspace in the relevant folder and port number resided in the file msmdsrv.port instead of msmdsrv.port.txt

    no connection could be made because the target machine actively refused it
    No such host exists

    1. Hi Nadir,

      Welcome to BIInsight and thanks for your feedback.
      Please note that is you have more than one Power BI Desktop file open, then you’ll see more folder starting with “AnalysisServicesWorkspacesXXXX”.
      It also happens to have more than one folder while you have just one opened Power BI Desktop if a file crashed or have been forced to close for any reason.
      Please read more finding Power BI Desktop local port number here.
      The reason that you get the “NO connection could be made…” error is most likely because you have already closed the Power BI Desktop that you are trying to connect to.
      Please update us with your findings.
      Cheers

  4. Hi everyone, Does somebody know how to automate this process to export more than 1 table at the same time?
    I don’t want to repeat this process for every table that I have on PowerBI.

    1. Hi Diego,

      Welcome to BIInsight.com.
      I haven’t looked into automating the whole process as the local port number is a random port number assigned to the local SSAS instance running in behind the scene, so it sounds like to be a little bit tedious process.
      However, there is an option that be of your interest.
      The latest version of DAX Studio has a very cool option of exporting the whole model to CSV or SQL Server.
      Here is how it works.
      Hope that helps.
      Cheers

  5. Hi,

    I notice the imported table columns are not arranged in the same order as what I see in PowerBI Data View, is there a way to make it the same order?

    1. Hi there,

      Welcome to BIInsight.com.
      Well, the columns are actually the same sort as far as I can see in my test environment.
      Are you sure you are looking at the same table in the Power BI Data View?

      Cheers.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.