Why do we need to export data from Power BI to Excel?
Although Power BI is being increasingly used by finance professionals, teams still have to produce static reports in Excel to format and analyze their data.
So how do you export data from Power BI to Excel? Read on to find out.
In this article Roxanne, Power BI consultant at F31, will walk you through the different methods available to export Power BI to Excel.
There are three native options for exporting data from Power BI to Excel; firstly, exporting data from a Power BI visual to Excel for simple data extraction. Alternatively, analyzing in Excel for exporting large data sets and refreshing data directly in Excel. Finally, getting data from within Excel, a quick but less efficient method.
Discover UpSlide’s Power BI Link: the easiest way to export Power BI visuals to PowerPoint or Word for easy integration into your presentations and reports.
Export data from a Power BI visual to Excel with “Export Data”
Here is how to export data from an online Power BI report to Excel:
- Go to app.powerbi.com and choose a report you have access to
- Click on “…” at the top right of the desired visual, then click the “Export data” button
- Choose which data format you’d like to export: with the current layout, summarized data, or underlying data.
- The report author can limit certain options, for example, removing the ability to export as ‘Underlying data.’
You can now edit your data or re-make graphs directly in Excel.
This first type of Power BI export to Excel is simple but limited as:
- You can only export data from one visual at a time, in a new CSV.
- You cannot update the data in Excel as the Power BI report evolves.
- You can only export data related to a Power BI visual, it is not possible to extract a table, because the data and the model cannot be viewed in the published version.
However, it is possible to export a table from the Power BI desktop to Excel: in the “Data” view, select a table, then right-click and “Copy” for a column, or “Copy Table” for the entire table. Simply paste the data into an Excel file to retrieve it.
Export large data sets from Power BI to Excel with “Work in Excel”
The second way to export data from Power BI to Excel can handle whole data models, plus offers the advantage of being able to refresh the data directly in Excel! This will help when you want to export large amounts of data.
Here is how to connect your data model to a blank Excel file with the “Analyze in Excel” function:
- Go to app.powerbi.com
- Open the report which contains the data set you want to export
- Click “…” to the far right of the ribbon above the report
- Select “View dataset”
- Under ‘Work in Excel,’ click ‘Analyze’
This downloads an Excel file in OneDrive, enabling you to view the workbook in Excel for the web. From here you can create PivotTables, visuals, etc. directly in Excel from the data derived from the Power BI data cube.
For example, when you want to create a new PivotTable in the Excel file (“Insert” > “PivotTable” tab), simply choose “From Power BI” and then connect to the desired dataset from the right-hand pane.
To refresh the data from the Power BI report, simply go to the “Data” tab and click on “Refresh All.”
Analyzing in Excel gives users autonomy and allows them to grasp the power of all Power BI’s possibilities.
This second type of Power BI export to Excel has the advantage of being semi-dynamic, yet it’s still limited as:
- It generates a new file, with an unfamiliar extension (.ODC, while Microsoft recommends to use the .XLSX extension).
- Measurements from data models must be created beforehand as it is not possible to create them in a PivotTable.
Create PivotTable by retrieving data from within Excel
Here’s how to access Power BI data from within Excel:
- Go to the Excel “Data” tab and select “Get Data”
- Choose “From Power Platform” in the drop-down menu and then “From Power BI”
- Select a dataset from the right-hand pane to create a PivotTable in Excel
- Choose fields from the PivotTable Field List to display
Whilst this is a quick, easy way to export data from Power BI to Excel, it isn’t the most efficient.
This is because selecting fields from a PivotTable Field List can make it difficult to visualize and understand the data set, especially if you didn’t create the Power BI dashboard yourself. Clicking through the various PivotTable fields to create your intended table in Excel could cost more time in the long run. Plus, it’s difficult to use Excel formulas on a PivotTable, which is limiting when building a report.
Create Table by retrieving data from within Excel
Microsoft recently announced a new way to export Power BI from within Excel. Here’s how you can leverage Power BI data to create a table in Excel:
- In Excel, go to the “Data” tab and click “Get Data”
- Similarly to the above option, choose “From Power Platform” and then “From Power BI”
- Choose your dataset from within the “Power BI datasets” in the right-hand pane, and then select “Insert Table”
- Build the table by easily selecting your values and filters.
If you’re a frequent Power BI user, this is the more efficient, easy way to “Get Data” from within Excel. The user-friendly pane is more similar to Power BI, making it easier to navigate and preview your table as you build it. Plus, the data output is built as a standard Excel table, meaning you can easily apply formulas, unlike the first option.
However, this way of exporting Power BI data to Excel is still limited. There is no easy or quick way to amend your table if you made a mistake when building it or if you’d like to edit your values. In this scenario, you’d either have to confidently amend the DAX language from within this clunky pane, or start the export all over again.
What is the best way to export Power BI to Excel?
Option 1: Export data from a Power BI visual – it is useful when you are simply looking to obtain the data underlying a visual on a Power BI report.
Option 2: Work in Excel – it is more suitable if you want to study your Power BI datasets. By importing the entire dataset, you have more flexibility, as long as you make sure that your metrics are created in advance so you can use them in your tables and graphs.
Option 3: Create PivotTable in Excel – it’s a relatively quick and easy way to export Power BI data to Excel. However, if you didn’t create the Power BI dashboard yourself, selecting the correct PivotTable fields could be time-consuming.
Option 4: Create Table in Excel – it offers more flexibility than creating a PivotTable, but still has its limitations. If you’re not well-versed in DAX language or Power BI, this option could be time-consuming.
How to export Power BI to PowerPoint or Word
Good news! You can now export Power BI visuals to PowerPoint or Word using UpSlide’s Power BI Link! This feature allows you to access your Power BI reports and dashboards directly from PowerPoint or Word, choose the visuals you need and insert them into your presentation in a few clicks.
Once your Power BI is linked to your PowerPoint or Word, you can also update your entire presentation or specific visuals with the latest data by simply clicking the ‘Update Links’ button.
UpSlide’s Power BI Link enables you to reduce your time spent on low value-added tasks and be sure the data in your presentation is always up-to-date.
Plus, you can easily change slicer values on all or selected visuals with our easy dropdown menu. Duplicate entire reports for different business units or time frames simply by editing slicers within PowerPoint and Word.
Do you want to know more about Power BI?
Here are some resources to help you learn more about Power BI:
- Read the article “How, and why, do finance professionals use Power BI?“
- Get a full Power BI training session by F31 experts
- Access Microsoft’s Power BI Guide