How to Export Power BI Data to Excel (Step-by-Step)

Written by: Kasper Langmann, Microsoft Office Specialist.

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.

Excited? Let’s get into it!

*This tutorial is for Power BI Online (also called “Power BI Service”). If you have Power BI Desktop, then click here and go to the online version.

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

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:

A tile in Retail Analysis Sample Dashboard in Power BI

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’:

Export to .csv option on a dashboard tile

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.

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:

A report to export in District Monthly Sales report in Power BI

Hover your mouse over the report until you see the ellipsis on the upper-right corner.

For some cases, you have to right click the report, and then click “Show as a table” or you won’t see the ellipsis.

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.

For this example, let’s export the underlying data in .xlsx format:

Save the file and open it in Excel:

How the exported report data looks on 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.

To demonstrate, let’s open the ‘Store’ table:

Store table of Retail Analysis Sample in the data view

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.

Ref: https://spreadsheeto.com/export-power-bi-excel/