Update 2021 March:
You can now export the data direct from Power BI Desktop using my tool, Power BI Exporter. Read more here.
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. 
Discover more from BI Insight
Subscribe to get the latest posts sent to your email.
Hi,
this is great!
But is there a way to remove the “TABLE[]” from Columns Names?
Thanks,
JM
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?
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
Thank you so much!
How can I rename the name of excel ? currently the excel downloads as “data”
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.
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
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
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.
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
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?
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.
Thanks for a great run-down of exporting to excel
Is it possible to add a filter somehow to the connected table when scripting the odc file. I have a very big table in PowerBI and I reach the maximum no. of rows imported. I don’t need all the rows so maybe some sort of filter solution could be applied instead of using the SQL-server method.
Thanks
Hi Cecilie,
Welcome to BIInsight.com.
I’m happy to see you find this post useful.
Re. filtering in the DAX query, for sure you can filter the results to fit the Excel row count limit.
So you should only use a DAX query which filters the results. Something like this:
EVALUATE
FILTER(FactInternetSales //Your table name here
, Year(FactInternetSales[orderdate]) >= 2012 //Your filter conditions
&& FactInternetSales[SalesAmount] > 500
)
The above query is looking at the FactInternetSales table and putting filters on data to only bring those rows that their OrderDate year is greater than or equal to 2012 and also their SalesAmount is greater than $500.
Hope that helps.
Cheers
Hi,
I’m trying the step where you EVALUATE a table from Power BI, and am using the following “EVALUATE ‘z Date Last Refresh’”, however get the following error message when I open the .odc file in Excel; “Query (1, 12) The syntax for ‘Date’ is incorrect. (EVALUATE z Date Last Refresh).
Any ideas why that is happening, as I have followed the instructions exactly?
Thanks
Hi Naveed,
Welcome to BIInsight.com
Please make sure you do not put your DAX query in double quotes.
You have to write your DAX query directly in the “odc:CommandText” block like the below:
Hope that helps.
Cheers.
Please note that the Power BI Desktop installed from the Microsoft Store has a different path for its working files. Instead of using “%LocalAppData%\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces” you’ll need to use “%UserProfile%\Microsoft\Power BI Desktop Store App\AnalysisServicesWorkspaces”.
Thanks for the article – very helpful!
Great article; worked like a charm. However, is there a way to import a Power BI data model into Power Pivot (Excel) so that the DAX formulas (which I wrote in Power BI) are maintained (in Power Pivot)?
I am am getting the column names in the excel file but now the values. can you please help me why it is happening>
EVALUATE FILTER(Amortized, Amortized[MeterCategory]=”Virtual Machine” &&(Amortized[BillingPeriodEndDate])=”1/31/2019″)
I have my date in text format
Hello,
Can I import only certain columns?
Thanks
Hi Tiago,
Welcome to BIInsight.com.
You certainly can, check out the “Copy Data from Query Editor in Power BI Desktop and Paste it to Destination” section on this post to see how.
Cheers.
Hi! Thanks for this post.
I was wondering if there’s a way to import the whole data model, not just a table. I mean, import from Excel the whole data model created in Power BI, as a pivot table, and stablish the connection to be available anytime from anyworkbook (and refreshing it anytime data is upload to the model!). Thanks!!
Hi Florencia,
Welcome to BIInsight.com.
I think what you’re after is connecting from Excel to the Power BI model and NOT importing data into Excel.
If that’s what you’re after then you certainly can, have a look at this post and also this one to find out how.
Hopefully they help.
Cheers.
I’m using power pivot to provide Excel based analysis referencing Power BI Datasets. I use Analyze in Excel to get me the data source coding necessary to switch if from a basic pivot table (with all the known issues) to a Microsoft Analysis Services driven Power Pivot.
When the ODC file is generated by clicking Analyze in Excel, I open the file and go directly Power Pivot/Manage, I click on Existing Data Sources and double click on the workbook connections “api.powerbi.com” reference. I copy the entire connection string, then cancel.
I then click on “From Other Data Sources” and scroll down and select “Microsoft Analysis Services”.
I paste the earlier string I copied into the Server or File Name. I click on the drop down arrow of the Database Name and select the now displayed code.
Click next. I just enter a basic Evaluate MDX command. I purposely gave my query a short name in Power BI so I wouldn’t have to deal with those long field names. My query was just call “PO” hence the MDX statement was “Evaluate PO”. Someone mentioned earlier about cleaning up field names and there is MDX code you can use to name the fields. I didn’t think it all that practical, but it can be done.
In my case the results are 100,000 records almost instantaneously available and loaded in the data model. A Power Pivot can be run, measures can be built, and all value fields will sum without any modification. The power BI Data Source is scheduled to refresh against SAP 4 times a day and the Excel Power Pivot is posted on Share Point allowing multiple user reference.
Hi Charles,
Welcome to BIInsight.com and thanks for your valuable comment.
I’m sure it saves a lot time for whoever who is looking into a similar scenario.
Cheers.