Analyze in Excel for Power BI Desktop is an external tool for Power BI Desktop that with a single click creates a new Excel PivotTable connected to the data hosted in Power BI Desktop.

By installing Analyze in Excel for Power BI Desktop, you can install a dedicated external tool that creates an Excel file connected to the data in the Power BI Desktop model.

When you have a data model in your PBIX file, by clicking on Analyze in Excel you open a new Excel file connected to the data model hosted in Power BI Desktop.

Now you can navigate the Power BI model using an Excel PivotTable.

Limitations

You will experience several limitations when using the Analyze in Excel for Power BI Desktop external tool:

  • You cannot close the Power BI Desktop window.
    • The connection to Power BI Desktop is lost as soon as you close the Power BI Desktop window.
    • If you refresh or navigate the PivotTable after you close Power BI Desktop, you get a connection error.
  • You can save the Excel file, but you cannot refresh it.
    • Same problem as described in the previous point: the connection changes every time you close and open Power BI Desktop.

Notes about previous versions

Versions 1.0.x of this tool was saving the connection string into an ODC file, which was following opened by Excel. This technique is also used in following versions if the alternative technique to create a new model in Excel fails for any reson.
If you use a version saving the ODC file, the following warning is displayed as a standard security measure because Excel does not automatically trust the ODC file containing the connection.

You can safely click Enable, or you can open the ODC file with Notepad to make sure that there is no malicious content in there. The AnalyzeInExcel.odc file is created by the tool to establish the connection between Excel and Power BI Desktop.

Open-source

The Analyze in Excel for Power BI Desktop tool is entirely free and open-source with MIT license. You can find the source code at https://github.com/sql-bi/AnalyzeInExcel.

The code and the installer are built and digitally signed by SQLBI. This way, as an end user you can have the tool available in your Power BI Desktop in just a few seconds.

If you are a developer and you want to contribute to the project, you are very welcome to do so. Read the following sections for ideas for future versions.

Future possible directions

The first version of this tool was a relatively simple project. Future versions of this tool could have the following features:

  • Create Excel Report: instead of creating an empty Excel file, create an Excel file starting from a template.
  • Open Excel Report: instead of creating a new Excel file, open an existing Excel file and replace the connection so that it works with the current Power BI Desktop window.
  • Excel Add-in to connect to Power BI Desktop: Excel opens the Power BI Desktop file that is necessary to establish a connection – a connection created in a previous session started by Analyze in Excel for Power BI Desktop.

The implementation of these additional features depends on people’s contributions. Additional contributors would be greatly appreciated. If you know C# and VSTO and you love Power BI and Excel, this project is for you!