Export Power BI Reports to Excel (Export Power bi to excel)

This Power BI tutorial, we will also disucss different approaches to Export Data in Power BI to excel. We will discuss how to export data from Power BI desktop to excel, Export Data from Power BI Dashboard to excel and Export Data from Power BI Report to excel. We will also see, Power BI Export to Excel Limitations.

Also, We will see Power BI Export underlying data details, how to enable the Export option in Power BI Desktop and what are the limitations and considerations in Power BI Export to Excel.

Learn how to export Power BI reports to PDF.

Different approaches to Export Data in Power BI

In Power BI, you can export the data by using three different approaches as:

  • Export Data From Power BI Desktop
  • Export Data from Power BI Dashboard
  • Export Data from Power BI Report

Power bi desktop export to excel

To export the data from the Power BI Desktop, Click on the visual which you want to export.

Once you will click on the visual, you can see the three dots () which are present at the top right corner of the visual as you can see in the below screenshot. Select Export data from the drop-down.

export power bi dashboard to excel
export power bi to excel

Now it will ask you to save the Excel file. For that, you choose your location where you want to save the excel file and Select the Excel File type as CSV. Then click on Save.

In the below example, I have saved the file name as IncomeTaxRate with the type of CSV File.

export data from power bi desktop to excel
Power bi desktop export to excel

When you will open the Excel file on your desktop, you can see the table like the below screenshot.

As I have saved my file with IncomeTaxRate name, that’s why the Excel spreadsheet name is appearing with the same name as shown below.

power bi export to excel
export power bi to excel

Export Data from Power BI Dashboard

Similarly, you can export the data from the Power BI Dashboard. For that, Go to your particular dashboard where the visual is present that you want to export.

Once you will go to the Power BI Dashboard page, Select your visual and click on the three dots () which are present at the top right corner of the visual. Then select Export data option from the drop-down menu as shown below.

export power bi dashboard to excel
export power bi to excel

When you will select the Export data option, then the below window will appear where you have to choose the Summarized data and the File format as .xlsx (Excel) as shown below. Then click on Export.

Once you will Export, the Excel file will be downloaded and it will be saved as .xlsx file type.

export data from power bi dashboard to excel

While you will open the downloaded Excel file (.xlsx), then the Excel spreadsheet will appear as the below screenshot. Here the Excel spreadsheet name will be the same as of the visualization name.

power bi export to excel xlsx
export power bi to excel

Export Data from Power BI Report

This is exactly similar to the Export Data From Power BI Desktop. Both are the same concept and processes. Here, One of the differences between them is, You have to export the data from the Power BI Report in Browser instead of the Power BI Desktop.

In the same way as Power BI Desktop, In the browser Power BI Report, Select your specific visual which you want to export. Then click on the three dots () and select Export data from the drop-down menu as shown below.

export power bi report to excel
export power bi to excel

Select the Summarized data and select the File format as .xlsx (Excel) from the drop-down. Click on Export.

Once you will click on the Export option, then the file will be downloaded and saved as per the name of the visualization.

export data from power bi visualizations
export power bi to excel

When you will open the Excel, then the spreadsheet will appear as the below screenshot. The Excel spreadsheet name will be the same as per the visualization name.

power bi export to excel with formatting
export power bi to excel

Underlying data details in Power BI Export

This Export underlying data details will work depends upon the help of your admin or IT department. In the Reporting view (In Power BI Desktop and Services), the measure option will display in the Field list with a calculator icon.

One most important thing you should know is, the Measures will create by using Power BI Desktop where the Power BI service does not.

In the below Table, It represents all the Visual Contains and what you will see in the Export in Power BI.

Visual Contains In Export, What you will see
Aggregates You will see the first aggregate and the data (non-hidden) from the entire table.
Aggregates You will see the related data. That means when the visual uses data from other tables which are related to the data table that contains the aggregate (where the relationship is *:1 or 1:1)
Measures You will see all measures that are present in the visual and all measures from any data table containing a measure that is used in the visual.
Measures You can see all non-hidden data from tables which contains the measure (where the relationship is *:1 or 1:1)
Measures It will help you to show all the data from all tables which are related to tables which contains the measure with the relationship of *:1 or 1:1
Measures only You will see all the non-hidden columns from all the related tables to expand the measure.
Measures only It will help you to summarize data for any duplicate rows for model measures.

Enable Export options in Power BI Desktop

To use the Power BI Export to Excel, you must ensure to enable the export option which is present in the Power BI Desktop.

To enable the Export to Excel option, Follow the below steps:

  • Go to the Power BI Desktop.
  • Select File (from the top left corner) -> Click on Options and Settings-> Select Options.
power bi enable export data
  • Select the Report settings which is present under the CURRENT FILE.

Once you will select the Report settings option, you can see three choices under the Export data section as shown below. The three choices are:

  • Allow end-users to export summarized data from the Power BI service or Power BI Report Server
  • Allow end-users to export both summarized and underlying data from the service or Report Server
  • Don’t allow end-users to export any data from the service or Report Server

To do the Power BI Export to Excel, you need to enable the first choice as you can see in the below screenshot. Once all things are done, just click on OK.

power bi export underlying data

Power BI Export to Excel Limitations

One of the most important things is the Limitations and considerations in Power BI Export to Excel. You should know about each and every limitation which is applied to Power BI Desktop, Power BI Service and Power BI Pro and Premium.

The Power BI Export to Excel limitations are presented below:

  • The first thing you need to know is the Power BI permission. To do the Export to Excel in Power BI, you have to build a permission for the underlying dataset.
  • Your application should have a maximum 150,000 number of rows which will export from an import mode report to an Excel file (.xlsx file).
  • Your application (In Power BI Desktop and Power BI Service) should have a maximum 30,000 number of rows which will export from an import mode report to an Excel file (.csv file).
  • In the Power BI Visualization, When you will enable the Show items with no data option, then the Underlying data will not work during Exporting.
  • Also, For the below reasons, Underlying data of Power BI Export will not work as:
  1. If the data source is an Analysis Services Live connection.
  2. If the version is older than in 2016.
  3. If the tables in the model don’t have a unique key.
  4. If an administrator or report designer has disabled this feature.
  • 16 MB is the maximum amount of data that Power BI can export when using the DirectQuery.
  • In Power BI, the Custom Visuals and R visuals are not supporting currently.
  • In Power BI Export to Excel, the Excel spreadsheet name should be the same as the Visualization name.
  • If your .csv file contains Unicode character, then the text in Excel will not display properly. In that case, try to open the file in a Notepad and there you can see the Unicode will display correctly. When you want to open the file in Excel, just import the file into .csv.
  • To import the file into Excel, follow these below things:
  1. Open Excel.
  2. Go to the Data tab.
  3. Select Get external data > From text
  4. Go to the local folder where the file is stored and select the .csv.
  • If you are the Admin in Power BI, then you can disable the export to Excel of data option.

You may like the below Microsoft Power BI tutorials:

Hence in this Microsoft Power BI Tutorial, We discussed What are the different approaches to Export the Data, how to export the data from Power BI Desktop, Export the data from the Power BI Dashboard, Export the data from the Power BI Report.

Also, We saw Power BI Export underlying data details, how to enable the Export option in Power BI Desktop and what are the limitations and considerations in Power BI Export to Excel.

>