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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
- 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 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:
- If the data source is an Analysis Services Live connection.
- If the version is older than in 2016.
- If the tables in the model don’t have a unique key.
- 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:
- Open Excel.
- Go to the Data tab.
- Select Get external data > From text
- 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:
- Power BI Data Analysis Expressions (DAX) Tutorial
- Power BI Query Group Tutorial
- Power BI Change Data Type of a Column
- How to Split Columns in Power BI
- Power BI Buttons
- Enter Data into Power BI Desktop
- Power BI Workbooks
- Power BI Error: This content isn’t available
- Power BI On-premises Data Gateway
- How to share Power BI Dashboard and Report
- Microsoft Power BI Error: This content isn’t available
- Power BI Custom Visuals
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.
Hello Everyone!! I am Bhawana a SharePoint MVP and having about 10+ years of SharePoint experience as well as in .Net technologies. I have worked in all the versions of SharePoint from wss to Office 365. I have good exposure in Customization and Migration using Nintex, Metalogix tools. Now exploring more in SharePoint 2016 🙂 Hope here I can contribute and share my knowledge to the fullest. As I believe “There is no wealth like knowledge and no poverty like ignorance”