Power BI is a Business Intelligence (BI) platform. With Power BI, users can get data, analyze it, and create visualizations that help turn raw data into actionable information for their business. It’s normally the case that you want to import data from Excel to Power BI to create a dashboard or report, but there are times when you need to export from Power BI to Excel. In this article, we’ll look at some of those reasons and explain the options.
Why Export Power BI to Excel? Common Use Cases
Normally we’d be importing data from Excel or other data sources such as SQL Server so that we can create reports and dashboards. There are times, however, when you need to export from Power BI to Excel. Some of the more common reasons are:
- Perform your own analysis in Excel: You may be given a report or dashboard which contains data that you want to drill into and analyze yourself in Excel. This could include creating a pivot table or chart using different dimensions from those used in the Power Bi visualizations.
- Perform data validations: You may want to check the completeness or cross-check the accuracy of the raw data underlying a chart or table in a Power BI report. Exporting the data to Excel is a simple way to do this.
- Maintain your own set of archived data: In some cases, you may want to maintain an archive of some Power BI report data that you receive.
Whatever the reason, it is a relatively straightforward process, and in this article, we’re going to look at two options.
What is the best method to export from Power BI to Excel
We’re going to look at 3 ways to export from Power BI to Excel:
- Export Data from a Power BI visualization
- Copy table from a Power BI dataset
- Use the Analyze in Excel tool
The choice of which one to use depends on your requirements. If you just need to see the underlying data from a specific chart or table visualization in a Power BI report, then the ‘Export Data’ option is a good one. To use the entire raw dataset, you can copy the dataset table and paste it into Excel. If you want to view and interact with a Power BI dataset using pivot tables, charts, slicers, and other Excel features, then you should use the ‘Analyze in Excel’ option.
Option 1: The Export Data feature
You can export data from a Power BI visualization from either the Power BI desktop app or the Power BI service (ie app.powerbi.com/). The screenshots below assume that the export is done from a report in the Power BI service, but the process is essentially the same for both.
Let’s say you have a Power BI report showing sales data (this example was created in an earlier article, and you want to download the data behind the Actual Sales vs Forecast Sales table.
Hover your mouse over the top left of the visualization and click on the ellipses (…) to show the More Options menu, and then click on the ‘Export Data’ option.
You’ll now be prompted to choose the format of the data to be exported. The three options are:
- Data with current layout – export the data in the same layout as you see it in the report or dashboard, but without any icons, colors, or other formatting you added.
- Summarized data – exports the summarized data used to create your visual (for example, sums, averages, and medians).
- Underlying data – exports the raw data used to calculate the data in your visual.
Once you click ‘Export’, an xlsx file will be created, which you can open and start working on.
There are some limitations and other things to consider when using this option:
- Any filters that have been applied in the Power BI report will be applied to the data that is downloaded.
- The maximum number of rows that Power BI Desktop and Power BI service can export to a .csv file is 30,000.
- The maximum number of rows that the applications can export to a .xlsx file is 150,000.
- When exporting data to Excel, the speed of download of the generated workbook can vary depending on network bandwith. Large datasets can take a while to download.
- Export using Underlying data won’t work if:
- the version is older than 2016.
- the tables in the model don’t have a unique key.
- an administrator or report designer has disabled this feature.
- you enable the Show items with no data option for the visualization Power BI is exporting.
- If the visualization uses data from more than one data table, and no active relationship exists for those tables in the data model, Power BI only exports data for the first table.
Option 2: The Copy Table feature
You can export the contents of a dataset table using the Power BI desktop app (this feature is not available from the Power BI service).
From the Power BI desktop app, go to the Data view by clicking on the Data tab on the left-hand side.
You can then select a table from the Fields explorer and right-click to select the ‘Copy Table’ option.
Once the data has been copied, you can open Excel and paste the data into a new sheet.
Option 3: The Analyze in Excel feature
The Analyze in Excel feature is only available in the Power BI service (ie app.powerbi.com/).
There are 3 ways to access the Analyze in Excel feature:
- From a report, click Export > Analyze in Excel
- From the Datasets page, click on the More Options ellipsis (…) next to a dataset and click Analyze in Excel
- When viewing a dataset, click on the Analyze in Excel button in the top menu bar
Whichever way you access the Analyze in Excel feature, it should install automatically. Select Download when prompted.
The Power BI service generates an Excel workbook that contains a connection to the Power BI dataset and downloads this workbook to your computer.
The Excel workbook contains a pivot table. The available pivot table fields match the data model structure in the Power BI dataset. Let’s take a look at what the downloaded file looks like. Here are the contents of the Contoso Sales Sample dataset:
You can now go ahead and create your own pivot reports and charts, with filters and slicers.
There are some considerations and limitations that you need to be aware of:
- Only measurements (ie calculated fields) from the Power BI data models can be added as aggregated values to a pivot table. This means that you need to create the measurements beforehand in Power BI.
- You can save the Excel workbook you created with the Power BI dataset, just like any other workbook. However, you can’t publish or import the workbook back into Power BI.
- When a user opens the workbook that you’ve shared with them, they see your PivotTables and data as they were when you last saved the workbook. That data may not be the latest version. To get the latest data, users must use the Refresh button on the Data ribbon. And since the workbook connects to a dataset in Power BI, the first time users try to refresh the workbook, they must sign in to Power BI and install the Excel updates.
Summary
In this article, we’ve talked about how to export from Power BI to Excel. The most common use case for why you would need to export to Excel is when you need to perform your own analysis of the data behind a Power BI report or Dashboard.
There are three methods to export from Power BI to Excel. If you just need to export the data for a specific chart or table, you can use the Export Data option than is available for each visualization. If you need the full set of data from a Power BI report, you can copy an entire table from a Dataset and paste it into Excel. Finally, you can use the Analyze in Excel feature to export a full Power BI data model to Excel where you can use pivot tables and slicers to perform analysis.
If you regularly use Excel, SQL Server, and Power BI, check out our earlier article about connecting Power BI to SQL server with the help of SQL Spreads and Excel. SQL Spreads can save you time and effort, so download a free trial to see how it can help you manage your data environment.
how can I saved data automatically from power bi click button to sql server?
One of our earlier articles discussed 2 methods for writing back to SQL Server from Power BI.