Windows: 16.0.15128 Web: introduced 28-Mar-2022
You can create PivotTables in Excel that are connected to datasets stored in Power BI with a few clicks. Doing this allows you get the best of both PivotTables and Power BI. Calculate, summarize, and analyze your data with PivotTables from your Power BI datasets.
: PivotTables from Power BI datasets is a Microsoft 365 feature that's rolling out gradually. For more information, see When do I get the newest features. To use this feature, you must meet these requirements:
-
To create a PivotTable connected to Power BI, you must be using Excel for Windows or Excel for the web and have a Power BI license. It is also recommended that the file be stored in SharePoint or OneDrive.
-
To create or use a PivotTable connected to Power BI, you need to have permission to the underlying dataset. For more details, see the section More about Power BI connected PivotTables.
-
Select an empty cell in your workbook.
-
Select Insert, the arrow under PivotTable, and then select From Power BI.
: Depending on your organization's IT settings you might see your organization's name included in the button. For example, "From Power BI (Contoso)"
-
In the Power BI Datasets pane, choose an enterprise dataset that is available to you. This will create a PivotTable for you in a new spreadsheet of the workbook you're working in. This may take time depending on the size of data sets.
: On Excel for Windows, you can also go to the Data tab > Get Data > From Power Platform > From Power BI.
-
Once you've created your PivotTable connected to a Power BI dataset, you can:
-
On Excel for Windows, to upload an Excel workbook containing Power BI connected PivotTables to the Power BI apps, go to the Power BI service, and then under Get data, select Connect.
-
Microsoft Information Protect (MIP) Labels are Microsoft's Security and Compliance offering to protect your data from unintended leaks. Both Excel and Power BI support MIP Labels and can work together to ensure safety of your confidential data in your Power BI datasets. Whenever you create a new connection to a Power BI dataset from the PivotTable functionality described above, the Excel workbook would automatically inherit the label applied on the dataset.
: MIP Label inheritance does not currently work with other external connections available in Excel.
-
Excel PivotTables connected to Power BI datasets now support both drag-and-drop aggregation of numeric fields and proper sorting and filtering for date fields that are now recognized as date-and-time objects in both Excel for the web and Excel desktop versions >= [16.0.15601.20020].
-
Users can connect to datasets in Power BI if they have permission for the underlying dataset. A user could have this permission in several ways, such as having the Member role in the workspace containing the dataset, having a report or dashboard shared to them that uses the dataset, or having Build permission for the dataset, in either a workspace or an app that contains the dataset.
