Why do we need to export Power BI to Excel?
Although Power BI is being increasingly used by finance professionals, teams still need to produce static reports – at given points in time, for example for particular products or markets. Then comes the question of how to export data from Power BI to Excel.
There is nothing better than a classic Excel spreadsheet, to format or analyze your figures, which can be read by most people.
Option 1: Export data from a Power BI visual to Excel with « Export Data »
It is possible to export data from visuals or tables on a Power BI report to Excel. This is a simple export that allows you to check, analyze or perform quick calculations on a table in Excel.
Here is how to export data from an online Power BI report :
- Go to app.powerbi.com, choose a report you have access to
- Click on ” … ” at the top right of the desired visual, then click the “Export data” button.
- You will have the choice between downloading a CSV or XLSX file containing the data relating to the visual
- By default you will only have access to the data filtered by the “Summarized data” view – depending on your rights, you can also export underlying data (unfiltered data) by choosing “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:
- 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 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.
Option 2: Export large data sets from Power BI to Excel with “Analyze in Excel”
The second type of export from Power BI to Excel deals with whole data models, with the advantage of being able to refresh the data directly in Excel! This will help you to export large amounts of data.
- Open the report which contains the data set you want to export
- Click “…” to the far right of the ribbon above the report.
- Select “Analyze in Excel“.
This downloads an Excel file in .ODC (Office Data Connection: an extension stored using HTML and XML) version. From here you can create pivot tables, 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 “Use an external data source” and then connect to the desired data cube by clicking on “Choose Connection“.
To refresh the data from the Power BI report, simply go to the “Data” tab and click on “Refresh All“.
This second type of Power BI export to Excel has the advantage of being semi-dynamic but still limited:
- 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 because it is not possible to create them in a PivotTable.
Option 3: Connect Power BI to Excel with “Power BI Publisher”
⚠ Before April 2020, it was possible to make a Power BI export to an existing XLSX Excel file without going to app.powerbi.com thanks to an add-in called “Power BI Publisher”. Since then, this add-in is no longer available for download, as Microsoft has decided to remove the feature.
If you did not install Power BI Publisher before April 2020, please go directly here.
If you installed the add-on before Microsoft removed it, please note that it still works. Here is how to connect Power BI to Excel:
- Go to the Excel “Connect to Data” tab
- Choose the report or dataset you want to connect to, then “Connect“.
- You can now create analyses that will fetch their data directly from the cube in app.powerbi.com.
This third type of Power BI export to Excel was the best option before it was taken down:
Both option 2 “Analyze in Excel” and option 3 “Power BI Publisher” allow you to connect to the Power BI report data cube, but the last method had some significant advantages:
- Simplified experience, without going through powerbi.com.
- Can be done from an existing Excel file, without generating a new file
- Use of the .XLSX extension, instead of .ODC
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 – Analyze 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.
*Please note that option 3 – Power BI Publisher – is preferred to option 2 if you were able to get the add-in before it was removed.
Can you export Power BI to PowerPoint or Word?
Good news! You can now export Power BI to PowerPoint using UpSlide’s Power BI to PowerPoint link! This feature allows you to insert and update visuals from Power BI dashboards without ever leaving PowerPoint.
UpSlide has also developed a really useful link to open a Power BI report and import the visuals directly to Microsoft Word.
You can already download the tool for free here!
Do you want to know more about Power BI?
Here are some resources to help you learn more about Power BI:
- Get a full Power BI training session by Finance 3.1 experts
- Watch this video on “The Powerful Interactions between Excel & Power BI” by the Power BI Community
- Access Microsoft’s Power BI Guide