I’m really exited to share with you a new Excel Add-in that was authored by Bertrand d’Arbonneau and has recently been made available via SQLBI.com The Add-in called Power Pivot Utilities combines a number of existing tools into a single new tool bar in Excel as shown below (Excel 2013 and 2016 only). Everything could already be done using various bespoke existing tools and procedures, but there is huge value by bringing them all together into a simple to use UI like this. This new Add-In goes straight into my “must have tools” for Power Pivot in Excel.
There are currently 8 icons on the tool bar (edit: plus there are some hidden gems in the context menus). Let me explain what each item does.
1. Insert Pivot Table
This solves a problem with Excel 2013 where it is difficult to insert a new pivot table that is connected to the data model. I have previously provided VBA code to solve this, but PP Utilities adds this feature as part of the toolbar suite – nice.
2. List Table Relationships
This feature is completely new to me although I guess it could be done already using DAX Studio. It simply creates a new tab with a table that summarises the relationships in your data model. Great for simple documentation or just to check what is going on.
3. List Measures
This works the same as with Relationships. It creates a new tab and extracts all the Measures from your model in an easy to read table. But just wait for the killer button 6 further down the page!!
4. List Calculated Columns
I am sure you are getting the hang of this by now. Same as above but for Calculated Columns
5. Model Memory Usage
Takes the VBA code work done first by Kasper de Jonge and then Scott Senkeresty to extract the information from the underlying data model so you can see where all your space is being consumed in the data model.
6. Format Expressions with DAX Formatter
I love this one. It uses the DAX Formatter API to format the DAX code you extract in steps 3 and 4 above. After you do this, the DAX is so much easier to read.
I use the same API as I use to format DAX code posted by users in the forum at powerpivotforum.com.au
7. DAX Studio
Simply launches the DAX studio application. You will still need to install this application to use it – you can download it here. You can of course run the application by going to the Add-In menu, but it is nice to have everything on the one toolbar.
Finally there is some really good quality help on each of the tools which is a really nice professional touch.
9. Now for those hidden gems
Pivot Table Right Click Context Menu
As Dominik pointed out in the comments below, there are some hidden gems. After installing the Addin, you will have some new menu items on the right click context menu. Just select a pivot table and then right click. Note in the image below the before and after picks of what the Pivot Table context menu looks like.
My 2 favourite additions are the first item and the last item. The first one will format your pivot table in the old tabular layout – something I do a lot.
The second one adds data bars to the pivot, something I also do a lot.
Sheet Tab Right Click Context Menu
When you right click on the sheet tab, you will see 4 new menu options.
Slicer Right Click Context Menu
When you right click on a slicer there are more hidden gems. I really love the “Display Slicer Source” – very helpful
A Final Word
I have previously shared all of these tools and tips as bespoke solutions, but Power Pivot Utilities nicely combines them all together into a single set of value adding tools. I highly recommend it and you can download it here.
January 26, 2016 7:00 am