We are introducing a new way for you to explore and analyze live Power BI data in Excel. Before now, you could only analyze live Power BI data in Excel using PivotTables (with the Analyze in Excel feature) but with this new experience, you’ll be able to use Excel tables to analyze live Power BI data and answer critical business questions with familiar spreadsheet functions.
We have started to roll-out the capability. This new connected experience enables you to export refreshable data to Excel from a Power BI visual. The data loads into the spreadsheet grid so it's easier to use for most users. The data is refreshable because the generated Excel workbook contains a live connection to Power BI, so you can refresh the data without leaving Excel. And the new open also lets you export more data into Excel from Power BI.
This new export to Excel feature is one of many planned new connected features aimed at improving user productivity by enabling you to self-serve data while keeping your data refreshed in Excel and reducing your reliance on ad-hoc static data requests.
How do I export live Power BI Data to Excel?
Go to any report in the Power BI service (or https://www.powerbi.com), open More options… on any Power BI visual and select Export data from the menu. For example, in the Workforce Demographics Report, I want to analyze the Recognized Revenue & Estimated Forecast from SalesForce & Backlog data in Excel.
In the dialog that pops up after selecting Export data, select the Summarized data card and you will see a new option under the File format dropdown menu: .xlsx (Excel) with live connection.
To see the new .xlsx (Excel) with live connection option, you need to have Build permission for the underlying Power BI dataset.
After clicking Export, an Excel workbook containing the live Power BI data is downloaded to your computer. When you open the Excel workbook, it will be opened in read-only mode until you select the Enable Editing button in the warning message.
To load the data to the Excel grid, click Enable Content and an Excel table is visible on the grid. Learn more about Protected View in Office.
Note: Once you click Enable Content and the data is loaded to the Excel grid, anyone the workbook is shared with can view but not refresh the data. Before sharing the exported file with a colleague, you may need to open the file, pressing Enable Content, and loading the data into the Excel workbook and save the file. If you don't, the recipient will need to have Build permission on the underlying dataset to load the data when they open the file.
Now you can see the live Power BI data as an Excel table and use your familiar Excel spreadsheet formulas to perform ad-hoc analysis or apply formatting to the data.
Note: The ExportHeaders tab shows the filters applied to the Power BI visual from which the data was exported from. That tab is always visible on any new workbook of data exported from a Power BI visual.
You can view the Power BI connection in the Excel workbook by clicking Queries & Connections under the Data tab in Excel. The Power BI connection is visible in the right pane, and you can update your Power BI data by clicking Refresh in the pane or right-clicking on the Excel table and selecting Refresh. Any formatting applied to the Excel table is preserved after the data is refreshed.
If you are an advanced user, you can view the DAX statement behind the Excel table under Connection Properties in Excel.
Enabling users to export more data
The new .xlsx (Excel) with live connection option supports up to 500,000 rows of data. This is a substantial increase over the 150,000 rows supported in static export scenarios. Increasing the number of rows in export has been one of the top customers asks for the export area. We're excited by this capability because it substantially increases the row limit. In addition, since the export is live and connected, export users can be more efficient because they don't need to recreate their analysis from scratch. Instead, they can save a copy, refresh their data, and immediately start their analysis. So, they load more data and save time, which is a win-win experience.What are the general requirements for using this feature?
- The Users can work with Power BI datasets in Excel using a live connection tenant setting has to be enabled. Learn more about Power BI tenant settings or contact your organization’s tenant administrator.
- You must have Build permissions to the Power BI dataset or have at least a Contributor role in a Power BI workspace.
- You must have a Power BI license: Free, Pro or Premium Per User (PPU). Learn more about Power BI license types and capabilities.
- This feature is available for use in both Excel Desktop and Excel for the web.
- This feature is not available for live connect datasets against Azure Analysis Services (AAS) or SQL Server Analysis Services (SSAS).