Less than a month ago, Microsoft introduced the External Tools feature in the Power BI Desktop July 2020 release. By using DAX Studio, you were already able to create a PivotTable in Excel connected to the model hosted by Power BI Desktop. However, this would require three clicks (DAX Studio / Advanced / Excel). This is why I thought the External Tools feature was something many users would like to use without having to open – or even install – a larger tool like DAX Studio is.
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. The warning is 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.
Now we can navigate the Power BI model using an Excel PivotTable. I find this feature very useful to validate the data, and whenever I need to make quick calculations in Excel based on the data I have – if I do not have time to publish the model on powerbi.com to use the supported “Analyze in Excel” feature. However, I see Excel as a valid tool during model development. With the two windows side-by-side, I can quickly test the accuracy of the numbers while I edit the model and the DAX formulas on the Power BI side. In the Excel window, I have features at my disposal – like creating additional calculations – that I do not have in Power BI and are very useful for data quality control.
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.
- You might get an error if you have a Power BI report connected to an external Dataset or an Analysis Services database in Live connection.
- The version 1.0.1 already fixed most of these cases. Please submit an issue in case you find scenarios where the connection still doesn’t work with an external Dataset.
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. I am already busy with other projects. Additional contributors would be greatly appreciated. If you know C# and VSTO and you love Power BI and Excel, this project is for you!