Exporting Data from Power BI Desktop to Excel and CSV – Part 1: Copy & Paste and DAX Studio Methods
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.
One of the features that is asked a lot on Power BI community is how to export Power BI desktop data to Excel OR CSV.
Note: I’d like to make it clear that in this article we are NOT interested in exporting data from a visual in a report to CSV.
The first question lots of you might have is “How on earth someone wants to export data from a Power BI Desktop model to Excel OR CSV?”.
Power BI brings Power Query, Power Pivot, Power View and Power Map together in one piece of software. So why not using Excel at the first place to load data from the source? There might be lots of other questions about the reasons that someone wants to export data from Power BI Desktop model to Excel (or any other destinations). The reason could be one of the following that someone wants to export data from Power BI Desktop model to other destinations like Excel, CSV, SQL Server etc.
- For some reason you have just a Power BI Desktop file (PBIX) and you don’t have access to the data sources and you need to provide the data to someone who is not familiar with Power BI
- You Power BI Desktop consolidates lots of different sources in a single model and it would be very hard to get the same output as you get in Power BI Desktop model in Excel. So an export feature can be super handy
- You might have done lots of complex transformations in Power BI Query Editor and replication the same logic on the source system could be much more complex and time consuming, so again exporting data from a current Power BI Desktop model makes sense
- You have a bunch of calculated columns created in DAX and you don’t want to go back and redo all the hard works you have already done in Power BI in another environment like Excel
- You might want to use the current Power BI data in Cortana Analytics
- You are just curious to see if it is possible
- None of the above!
But, the reality is that regardless of the reason, lots of people still want to export data from Power BI Desktop to different destinations. So let’s have a look at different workarounds until this feature is not available in Power BI. I’ll explain different ways to export Power BI Desktop data in a series of articles. In this post you learn how to copy Power BI Desktop data to a destination file like Excel or CSV without any third-party software involved. I also explain how easy you can export Power BI Desktop data to CSV using DAX Studio.
Copy Data from Data View in Power BI Desktop and Paste it to Destination
The easiest workaround is simply copy/paste data from Data view in Power BI Desktop.
- Open your Power BI Desktop model
- Switch to Data view by clicking on Data tab
You have now 3 options to copy data:
- Right click on a desired table and click “Copy Table”
- Click a desired table then from the ribbon, click “Copy”
- Click a desired table, right click on data area then click “Copy Table”
- Now open a new Excel file and paste the copied data
- You can copy the entire table, including all DAX calculated columns, very easily and paste it on a destination like Excel or a text editor and save it as TXT or CSV files
- You copy transformed and probably cleansed data in case you have done any data transformation in Query Editor
- No third party tool is needed/involved
- If for any reason you want to paste the data in SQL Server, this might not be the best way to get the job done
- This way is good for small volume of data. If you want to copy a larger amount of data than some thousands rows, say even 64,000 rows, then the copy process might take a long time
- There is always a risk of missing data as we are copying data into Windows clipboard
Copy Data from Query Editor in Power BI Desktop and Paste it to Destination
Another easy workaround is to copy data from Query editor:
- On Power BI Desktop click “Edit Queries” to open Query Editor
- Select a desired query
Now you have 2 options:
- Click on the tiny table button located on the top left of the table and click “Copy Entire Table”
- Press Ctrl+A to select all columns, then press Ctrl+C to copy data or right click on a column header and click “Copy”. You can select multiple columns by pressing Ctrl and clicking on column headers in case you don’t want to copy the entire table.
You can also select different levels of data transformation to be copied.
- Now you can paste the data to Excel
- You can copy the entire table, very easily and paste it on a destination like Excel or a text editor and save it as TXT or CSV files
- You can copy multiple selected columns rather than copying the entire table
- Not only can you copy transformed data in case you have done any data transformation in Query Editor, but also you can decide which level of transformed data should be copied
- No third party tool is needed/involved
- DAX calculated columns are NOT included
- If you use Ctrl+A, you select all columns to be copied which includes complex columns automatically added to the table. For a table like DimDate which potentially has lots of relationships with other tables, you probably don’t like it.
- It’s not that easy to export data in other destinations like SQL Server
- Poor performance in copying large amount of data
- Risk of missing data as we are copying data into Windows clipboard
Export Data from Power BI Desktop to CSV or TXT Using DAX Studio
Update: If you’d like to export the model data as a whole, check this out.
Exporting data from Power BI Desktop to CSV using DAX Studio is super easy. An advantage of using DAX Studio is that it works great regardless of the amount of data you want to export. The other advantage is that you can literally export a query output to CSV which can be very helpful if you don’t want to only export the entire table, but a specific query.
Here is how to get the job done in DAX Studio:
- Open a desired Power BI Desktop model
- Open DAX Studio
- Click “PBI / SSDT Model” then select your Power BI model from the list then click “Connect”
- Write a desired DAX query, I’m not going to explain DAX query language in this article, but you can simply write “EVALUATE TABLE_NAME” which “TABLE_NAME” is the name of the table you want to export. So in our case it is “FactInternetSales”
- Run, or press F5, to execute the query
- Click “Results” tab and make sure you get the expected results
- Click “Output” from the ribbon and select “File”
- Run the query
- Select a folder and enter file name to save the results in TXT or CSV then click “Save”
- Now DAX Studio start writing data into the output file
- After DAX Studio finished writing data you may open the file
Bottom line: You can easily copy/paste your Power BI Desktop data to a destination like Excel or notepad from Power BI Desktop as explained above. You can also export Power BI data using DAX Studio to CSV very easily. However, the first approach it is useful only when you want to copy small amount of data. DAX Studio is super easy and very powerful tool to export data from Power BI Desktop to CSV or TXT files. But, what if you need to load more data to Excel or even SQL Server?
In my next post I’ll explain how to directly import Power BI Desktop data to Excel, so stay tuned. 
How to Export Power BI Data to Excel (Step-by-Step)
Exporting data from Power BI to Excel only takes 1 to 2 clicks!
Though normally, it’s the other way around — importing data from Excel to Power BI.
Nevertheless, it’s super easy and intuitive to do.
In this article, we’ll show you the step-by-step process in exporting Power BI data to Excel.
We’ll also include an old-school copy-paste method that can only be done from the Power BI desktop version.
Kasper Langmann, Co-founder of Spreadsheeto
Excited? Let’s get into it! ��
*This tutorial is for Power BI Online (also called «Power BI Service»).
Table of Content
Introduction: Exporting Data
Like what you read earlier, what normally happens is that you import data from Excel to Power BI as an .xlsx or .csv file to run insights and analytics on it.
However, there could be reasons why you would want to export data from Power BI.
Some of these include:
- Double-checking the actual data but you don’t have access to the data sources
- Having the need to provide the data to an analyst (or someone) who is more familiar and comfortable with Excel
- Using the current Power BI data in Cortana Analytics
Whatever your reason is, it’s good to know that exporting data from Power BI is possible even without the use of third-party software.
Kasper Langmann, Co-founder of Spreadsheeto
Exporting Data from a Power BI Dashboard
A Power BI dashboard is like a page where you can highlight your data to tell a story. You do this by pinning tiles — visualizations — from reports.
Let’s say you have the following dashboard and you would like to export the data behind the ‘This Year’s Sales, Last Year’s Sales’ tile:
Hover your mouse over the tile and you’ll notice an ellipsis or a 3-dot icon (…) fade in on the upper-right corner.
Click the ellipsis and select ‘Export to .csv’:
Save the file and open it in Excel:
Take note that if you filter the visualization before exporting, you’ll also get the filtered data.
Kasper Langmann, Co-founder of Spreadsheeto
Exporting Data from a Power BI Report
The tiles you pin on dashboards came from reports. A Power BI report contains visualizations that represent the insights from a dataset.
Basically, the steps into exporting data from a dashboard, report, and even dataset are similar.
Let’s say you’re looking into your ‘District Monthly Sales’ and you would like to export the data behind the ‘This Year Sales by StoreNumberName’ report:
Hover your mouse over the report until you see the ellipsis on the upper-right corner.
Click the ellipsis and select ‘Export data’:
Power BI will ask specific questions about your export data:
- Export summarized data or underlying data
- File format either in .xlsx or .csv
What you have to look out here is the size of your report. The first one — .xlsx — supports a maximum of 150,000 rows compared to the 30,000 rows of .csv file.
Kasper Langmann, Co-founder of Spreadsheeto
For this example, let’s export the underlying data in .xlsx format:
Save the file and open it in Excel:
Like in exporting data from a dashboard, filters will also apply to the export data.
Copy Table in Power BI Desktop
When talking about Power BI Desktop vs Power BI Service (the online version), you’ll notice a lot of differences.
One of which is the data view in Power BI Desktop.
This method requires that you have a Power BI Desktop since you’ll be copying tables in the data view.
Let’s use the same dataset we’ve used in the previous sections. To go to the data view, press the table icon on the left-hand sidebar:
You’ll then notice a table on the center with the field options on the right-hand sidebar.
Opening a specific table by clicking its name on the right-hand sidebar will list out its available fields under the table’s name and display the table in the center.
Kasper Langmann, Co-founder of Spreadsheeto
To demonstrate, let’s open the ‘Store’ table:
If you like to copy that table, simply right-click on it on its name on the right-hand sidebar and select ‘Copy Table’:
Open Excel and paste the table either by clicking the ‘Paste’ icon or by simply pressing ‘Ctrl’ + ‘V’ on your keyboard:
This method lets you copy entire tables including the DAX calculated columns.
However, you might not want to do this with larger tablers. It takes more time to copy larger tables and paste them into Excel.
Limitations and Considerations in Exporting Data
There are certain limitations and considerations in exporting data that apply to both the Power BI Desktop and Power BI Service versions as well as in all the plans including the Power BI Pro and Premium plans.
Microsoft laid them out in detail in their Power BI Documentation.
Here’s a summary of those limitations and considerations:
- You need to have build permission for the dataset before you can export data from a visual of that dataset.
- The maximum number of rows that Power BI can export to a CSV file is 30,000 and 150,000 to an XLSX file.
- To export underlying data, your version should be older than 2016 and that the tables in the data model don’t have a unique key.
- You can’t export underlying data if the administrator has disabled this feature. Exporting won’t also work if the ‘Show items with no data’ option is enabled for that specific visualization data to be exported.
- The maximum amount of data that Power BI can export is 16 MB uncompressed data.
- Only data from 1 table is exported if there’s no relationship between the data even if data from that visualization came from different sources.
- You can’t export data from custom visuals and R visuals.
- If a field is renamed in Power BI, it’s possible that Excel would use the original field name.
- Some Unicode characters may no properly display in Excel.
- Administrators can disable data export in Power BI.
Wrapping things up…
As you can see, exporting data from Power BI to Excel is simple and easy that you can do it in less than 5 seconds. However, take note of the limitations and considerations so you’ll be able to export accurate data.
If you’re confused about the steps, download the free ‘Retail Analysis Sample’ from Power BI and follow the steps we’ve outlined in this article.
Kasper Langmann, Co-founder of Spreadsheeto