Build refreshable Excel reports with Power BI data

You've connected to a Power BI dataset in Excel by starting in Power BI with Analyze in Excel or in Excel with Get Data. Now that you're connected, Excel presents you with an empty PivotTable and Fields list from the Power BI dataset, and you're ready to analyze your data.

Excel with data connected

The Excel file has an MSOLAP connection string that connects to your dataset in Power BI. When you analyze or work with the data, Excel queries that dataset in Power BI and returns the results to Excel. If that dataset connects to a live data source using DirectQuery, Power BI queries the data source and returns the result to Excel.

With that connection to the data in Power BI now established, you can create PivotTables, charts, and analyze that dataset just as you would work with a local dataset in Excel.

Blank PivotTable for Analyze in Excel.

Refresh the data

You can refresh the Power BI dataset connection to update your reports in Excel in one of two ways.

  • Right-click anywhere in the PivotTable and select Refresh.

    Right-click anywhere in the PivotTable and select Refresh.

  • Select Data in the Excel ribbon and select Refresh.

    Select Data in the Excel ribbon and select Refresh.

    Important

    Refresh updates the Power BI connection string for the report you see on the sheet. Refresh All updates all the connection strings in the Excel workbook, including non-Power BI data sources.

Considerations and limitations

  • Excel PivotTables don't support drag-and-drop aggregation of numeric fields. Your dataset in Power BI must have pre-defined measures. Read about creating measures.
  • You may see an error message if the data is larger than 2 GB. In that case, either reduce the amount of data, for example by applying filters.

See the Prerequisites section of Connect Excel to Power BI datasets for other requirements when connecting to Power BI data in Excel.

Next steps