A Fabulous new Excel Add-In for Power Pivot - Excelerator BI

A Fabulous new Excel Add-In for Power Pivot

I’m really excited 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.

image

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.

image

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!!

image

4. List Calculated Columns

I am sure you are getting the hang of this by now. Same as above but for Calculated Columns

image

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.

image

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.

image

I use the same API as I use to format DAX code posted by users in the forum at http://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.

8. Help

Finally there is some really good quality help on each of the tools which is a really nice professional touch.

image

9. Now for those hidden gems

Pivot Table Right Click Context Menu

Edit: 30/1/16

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.

after

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.

sheet

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
slicer

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.

19 thoughts on “A Fabulous new Excel Add-In for Power Pivot”

  1. HI I was following the step-by-step instructions for DAX Studio in your Document “DAX Studio for Power BI Getting Started” – I am fairly advanced when it comes to Power BI and almost an “expert” when it comes to Excel. Obviously DAX Studio and PP Utilities are new to me.
    So, here is my problem. This is what I did and you can probably figure out where I went wrong.
    1. My PowerBI files based on Microsoft’s AdventureWorks data. All done and working fine.
    2. Open Excel
    3. Open DAX Studio (v. 2.16.2) and make a connection to the Power BI file
    4. DAX Studio recognizes my Excel file but then displays a message: Warning! PowerPivot Model not detected.
    5. My PBI file has a “relationship” Model and it seems to be fine.
    6. Then I create a query in DAX: select * from $SYSTEM.MDSCHEMA_MEASURES
    And it works fine, I remove all columns except D and N; Measure_Name and Expression, respectively. Then I try to do List Measures and get the message:
    “This workbook does not have a Model”

    Where – in life – and in Excel – did I go wrong???????
    THANKS for your help!

      1. Thanks for your help!
        However, in order to launch DAX Studio from within PBI, you need an “extension” called External Tools and it, requires a JSON File. I tried to make the JON file as simple as possible:
        {
        “name”: “DAX Studio”,
        “description”: “DAX Studio”,
        “path”: “C:\Users\Richard\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\DAX Studio\DAXStudio.exe”,
        }
        and saved as DAX Studio.pbitool.json

        When launching PBI it gives me an error. invalid json

        Obviously missing something.

        At Microsoft External Tool Register “help” page, it also includes these 2 lines:
        “arguments”: “C:\\pbiToolsDemo.ps1 -Server \”%server%\” -Database \”%database%\””,
        “iconData”: “image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsEAAA7BAbiRa+0AAAANSURBVBhXY/jH9+8/AAciAwpql7QkAAAAAElFTkSuQmCC”

        And of course I have no idea what edits those 2 lines require.
        Sorry to bother again!

      2. It’s me again and I am terribly sorry for all the questions.
        What’s happening now is that follow all your steps and get an error:
        “Could not load file or assembly. ‘System. Diagnostics. Tracing. Version=4.2.0.0 Culture=Neutral Public key…” Then it continues to state: “The located assembly’s manifest definition does not match the assembly reference……”

        I am sorry – but all I did was to follow your instructions – step by step. What is wrong now?

      3. Soon you will ban me from this Forum…. To be honest, I wouldn’t blame you.

        First: the issue with “Could not load file or assembly. ” has been fixed; Just reinstalled DAX Studio and that took care of that.
        However, now I am back to my original error message:
        “This workbook does not have a Model”
        Everything – prior to that in your Step-by-Step works fine, but when hitting “List Measures” in PP Utilities (in Excel), that’s message I get: “This workbook does not have a Model”
        Looking at the Connect Page in DAX, it looks good. PBI/SSDT Model is checked and has the correct PBI file….
        There are six measures in that Power BI file. So, that’s working, but anything I try in PP Utilities generates that error: “This workbook does not have a Model”
        So, again, what am I doing wrong?
        Thanks!!!!!!!!

        1. Well it’s not a forum, it’s my blog :-). This blog post is specifically for Power Pivot for Excel. From your earlier comments, you are not using Power Pivot for Excel. You need to start from Power BI Desktop where the actual data model resides and proceed from there. If your Power BI Desktop file is using a Golden Dataset, then that will not work either. You need the Power BI Desktop file that contains the data model.

          1. Thanks for your patience.. You are terrific!
            But I think I have followed all your points: The PBI file is one of those Microsoft sample files, AdventureWorks, and it works fine in PBI. After completing the file in PBI, I am launching DAX Studio from PowerBI. Then exporting it from DAX to Excel, per your instructions.
            Then I get this error.
            So, how can you tell if a Power BI (Desktop) file has a Model?
            I thought that *Model* was PBI’s new name for what they used to call Relationship… No?
            My PBI file has, of course, Relationships/Model, otherwise it would not work at all.
            So, I am not sure where the “Model-part gets dropped!”! Or is the Model in PBI a different flavor of “Model” than the one Excel says that is missing???
            Again, thanks for your angel like patience!

            1. The “model” is essentially the database structure including tables and relationships. In power bi desktop, click on the model view. If you can see tables, then you have a model. In Excel, click on Power Pivot and go to the diagram view. If you can see tables, then you have a model.

              Let’s just step back. What are you trying to do? Are you trying to get a list of measures out of Power BI Desktop into Excel? If so, I suggest you follow this approach.
              https://exceleratorbi.com.au/excel-workbook-connection-local-power-bi-desktop/

  2. Hi Bertrand,
    I’ve just discovered your tool and I’m very pleased with it.
    Nevertheless, may I suggest 2 little things regarding the databar tool:
    – Having the option to set the color (I changed the color code by myself in the vba code)
    – Having the option to delete it
    Otherwise, I didn’t have time to test it intensively but you already save my life 🙂

  3. Bertrand d'Arbonneau

    Good suggestion. I will add this feature to the next release. So far, the code I am working on is the following:

    Sub DisplayConnectedSlicers
    Dim oSlicer As Slicer
    Dim oPT As PivotTable
    Dim strSl As String
    On Error Resume Next
    Set oPT = ActiveCell.PivotTable
    If oPT Is Nothing Then Exit Sub

    For Each oSlicer In oPT.Slicers
    strSl = strSl & “‘” & oSlicer.SlicerCache.Name & “‘ on sheet ‘” & oSlicer.Shape.Parent.Name & “‘” & vbCrLf
    Next
    If Len(strSl) > 0 Then Call MsgBox(Trim(strSl), vbOKOnly, “Connected Slicers”)
    End Sub

  4. Wow thats just the tool i was looking for to ‘Display the slicer source’. Just occasionally you get the same field name in different tables which you then use as a slicer but then you can’t remember from which table the field you are slicing on came from. This resolves that.

    Please one suggestion which i think would be a wicked tool. Please create such that when you right click a Pivot Table there is the option ‘Display slicer names that are connected to this pivot table’. Just i have been working on a big report that used a master slicer tab in excel for selecting many slicers driving many different pivot tables across the report. Managing & checking which slicers connect to which pivots was mayhem. I know you can select the slicer settings to see which Pivot tables it is linked to but it is not possible the other way round from the point of view of the pivot table.
    If this option is currently possible then please someone enlighten me where & how.
    Crazy as it may sound but the way I did it was within the pivot table to use convert to OLAP formulas where then in the formula you can see the name of the slicers

    1. Bertrand d'Arbonneau

      David, I just discovered that there is a built-in function to do that. It is in the PivotTable Tools / Analyse tab: “Filter Connections”

  5. Wow! This will be great. I usually cut and paste my DAX formulas and do screen shots of relationships. This will really help the business users to check their workbooks without getting programmers involved.

  6. Thanks for the article Matt, very useful indeed.
    I read all their articles, but I don’t often go into the utlities section in SQLBI.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top