How to Export Power BI Data to Excel

Common Use Cases of Exporting Power BI Data and Reports into Excel

There are a few reasons why you would need to export data and reports from Power BI to Excel:

• Analyzing the data in Excel: Even if Power BI has superior capabilities to Excel in storing and processing data, Excel can still be useful for creating reports and dashboards using Power BI’s Analyze in Excel feature. Moreover, it may be more intuitive for Excel experts to learn Power BI gradually without abandoning Excel completely.
• Data exports: A colleague is interested in having the data behind the report, but they don’t have Power BI installed. Exporting the underlying data to Excel is an easy solution. 
• Cross-tool use: If you are working in a team with various data practitioners. Certain team members may use other tools, such as R or Python. Exporting Power BI data and reports to Excel will allow them to do just that.

Exporting Data from a Power BI Dashboard

Power BI Dashboards allow you to place visualizations, reports, and tables all in one place for an at-a-glance view. However, oftentimes, you may want to investigate the underlying data behind the visualizations in your dashboard or send it to a colleague. Here are the steps to export data from a Power BI Dashboard:

• Go to your Power BI instance and log in with your account’s credentials. If you don’t have an account, you will need to create one. 
• Go to the Power BI dashboard in question and choose the data visualization from which you are interested in exporting the data.
• Click More options (…) from the upper right corner of the chosen data visualization.
• Select the option Export to .csv.
• You can then open up this .csv file in Excel. 

To learn more about these file formats, check our blog post about CSV vs Excel. 

Exporting Data from a Power BI Report

Power BI Reports are often confused with Dashboards since they both contain data visualizations and tables. Reports can have more than one page and permit viewers to find different ways to filter, highlight, and slice the data. Moreover, they are a great option for creating comprehensive summaries of your to share with the stakeholders. 

There are the following steps to export data from Power BI Reports:

• Access the report from Power BI Desktop. If you don’t have Power BI Desktop installed, go here. 
• Choose the data visualization from which you are interested in exporting the data.
• Click the tile settings of the chosen data visualization 
• Select the option Export data.

Copying Power BI Tables into Excel

The steps to copy a Power BI Table to Excel are very simple and intuitive:

• Go to Power BI Desktop.
• Choose the Power BI table you want and go to the data view option on the left side panel.
• Right-click on the chosen table; in this case, it’s called “Sales.”
• Select the option “Copy Table.” 
• Create a new sheet in Excel and paste the content of the table by clicking the Paste icon or Ctrl + V

Exporting from Power BI into Excel with the “Analyze in Excel” Feature

In Power BI Service, there is an additional feature that brings a Power BI dataset into Excel. This can be useful for processing the data into Excel and then using the processed data in Excel to generate data visualizations. These are three different ways to utilize the Analyze in Excel function of Power BI.

1. Analyze in Excel feature from my workspace

My Workspace in Power BI is a view that contains a collection of datasets, reports, and dashboards you’ve created in the past. You can use the Analyze in Excel feature to export any of these datasets and reports into Excel. Here’s how:

1. Go to app.powerbi.com, where the collection of all your works will appear on your Power BI account.
2. Select Download from the menu. If you haven’t done this already, click Analyze in Excel updates. This operation is mandatory. Otherwise, the Analyze in Excel feature won’t work. 
3. Once you’re ready, choose the Power BI dataset you want to analyze in Excel.
4. Select More options (…) next to the dataset and then click Analyze in Excel.
5. When you open the new Excel file, enable the editing and content. You shouldn’t have problems if you have installed the Analyze in Excel updates. But if you still have issues, check this article for further guidance.

2. Analyze in Excel feature from the view of the dataset

An alternative method is to click the name of the dataset in your workplace.  A new page will be opened where you can select “Analyze in Excel” in the menu bar above the page.

3. Analyze in Excel feature from the report

The third and last way is to open a Power BI report and select Export → Analyze in Excel in the menu bar.

Limitations When Exporting Data from Power BI to Excel

While exporting data from Power BI to Excel is a powerful feature, there are some limitations and considerations to keep in mind:

1. Data size limits

• Row limits: By default, Power BI limits the number of rows that can be exported to 150,000 for summarized data and 30,000 for underlying data. For larger datasets, consider using Paginated Reports or Analyze in Excel to overcome these limitations.
• File size: Exports may fail if the resulting file size exceeds Excel’s limitations (e.g., 1,048,576 rows per worksheet in .xlsx format).

2. Loss of visual formatting

• Exporting data often strips away the formatting and conditional logic applied in Power BI visuals. For example, color-coding or custom formatting in Power BI tables and charts will not be preserved in Excel.
• Use the Data with Current Layout export option for table and matrix visuals to retain as much formatting as possible.

3. Data accuracy

• When exporting summarized data, Power BI applies aggregations (e.g., sum, average) based on the visual’s settings. If your analysis requires granular data, ensure you export the Underlying Data instead.

4. Data model relationships

• Power BI visuals often rely on relationships and calculated measures in the data model. When exporting, these relationships may not be explicitly visible, requiring additional setup in Excel to replicate the same insights.

5. Performance considerations

• Exporting large datasets can impact performance in Power BI and Excel. Use filters to limit the data exported or break down the dataset into manageable chunks.

6. Security and sensitivity

• Data sensitivity labels applied in Power BI are not carried over into Excel. Be mindful of organizational compliance policies and ensure sensitive data is handled appropriately after export.
• Utilize Microsoft’s Data Loss Prevention (DLP) policies to monitor and control data exports.

7. File compatibility

• The default export format for Power BI is .csv, which doesn’t support advanced Excel features like charts, pivot tables, or formulas. For advanced reporting, use Analyze in Excel or manually enhance the .csv data in Excel.

8. Automation vs. manual exports

• While tools like Power Automate can automate exports, setting up and maintaining these workflows may require technical expertise. Evaluate whether automation is worth the setup effort based on the frequency of exports.

More Power BI Resources

We hope you found this tutorial useful. This article discussed different ways to export Power BI data to Excel. This functionality can be very useful when you want to store and analyze the data outside Power BI.

If you’re new to Power BI or Excel, DataCamp offers a variety of resources to help you build your skills:

• Start your journey with Introduction to Power BI or dive deeper with the Power BI Fundamentals track.
• Learn how to create stunning dashboards and visuals with the Data Visualization in Power BI course.
• Enhance your Excel skills with the Excel Fundamentals track, or begin with the Introduction to Excel course.