Some time ago I developed a tool that allows you to automatically connect a local Excel Pivot Table to a local running instance of Power BI Desktop. You can read that original article here if you like. It works kind of like Analyze in Excel but using Power BI Desktop running on your local PC instead of PowerBI.com.
I recently updated the template I created back then so that it now also provides you with
- the memory usage of your Power BI Desktop data model, and
- the list of measures from your Power BI Desktop Data Model
This article explains how to install the tool and what it does.
First download the template
You can download the Excel template file here.
After downloading the Excel template, you should place the template in the location of your default personal templates. You may or may not have this location set (it is not set by default). To check, first go into Excel Options
and then make sure you have a location set (see mine below). You can set your location to what ever you like (somewhere in your documents folder makes the most sense.
Then simply place the Excel template you downloaded in this same folder as you set in the options above (make sure you unzip the file too).
Instructions for Usage
First open an instance of Power BI Desktop on your PC. There must be 1 and only 1 instance running on your PC for this to work.
Next you need to create a new Excel Workbook using the template you installed above.
The first time you use it you may need to “Enable Content” as shown in 1 below.
Then click “Refresh SSAS Connection” as shown in 2 above on the first tab of the workbook.
Because of the way I extract the data using Power Query, you will get a dialog like shown below. Just click Run to execute the query.
And then click Connect as shown below when asked about the credentials.
You will now have a Pivot Table that is directly connected to your instance of Power BI Desktop running on your PC (see my example in 1 below). This feature worked in my earlier version of the Local Host workbook, but now there are 2 new tabs – Memory Usage (see 2 below) and Measures (3 below).
The memory usage tab will give you a pivot table similar in approach and experience as has been developed before by Kasper de Jonge, Scott Senkeresty, Bertrand d’Arbonneau among others.
You can just expand and collapse the pivot table to see what columns in your data model are taking up the most space.
If you want to access a list of measures, simply switch to the Measures tab, then right click in the table (shown as 1) and then select Refresh (shown as 2 below).
There will be another native query that you will have to run – just click the button.
After the query is executed, you will have a list of all the measures in your Power BI Desktop file (see my example shown below).
And if you have Bertrand’s Power Pivot Utilities, you can even use the DAX Formatter button in that addin to format your measures as shown below.
Good luck with this tool – I hope you find it useful.