Power Pivot Utilities in an Excel add-in (based on a collection of VBA macros) that helps documenting PowerPivot models and facilitates the creation of pivot tables connected to the data model.
The author of this tool is Bertrand d’ARBONNEAU (firstname.lastname@example.org).
We are happy to host his component on SQLBI, providing this useful tool to a large audience of Power Pivot users. Bertrand will hopefully continue the development of the component, and we will keep the last version available in this page.
In order to use Power Pivot Utilities, unzip the files in a folder and open the PowerPivot_utilities.xlam file in Excel. You will add a ribbon named PP UTILS with the following tools.
This is a short description of the features available:
- Insert PivotTable: Inserts a pivot table connected to the data model of the active workbook. It produces the same result as the button in the PowerPivot management window, but with less clicks.
- List table relationships: Creates a static list of the table relationships that exist in the Power Pivot data model of the active workbook. The list is created in a new worksheet named “Model_Relationships”. If you modify the relationships after creating the table, you can invoke the macro again in the existing table will be overwritten after user confirmation.
- List measures: Creates a static list of all the measures that exist in the Power Pivot data model of the active workbook. Each measure is listed with its DAX expression and with the name of the table which it belongs to.
- List dependents and precedents measures: after all measures of a data model have been listed with “List measures” command, you can click a measure name in the table and press on one of the buttons in the ribbon (Dependents or Precedents in the Measures section). The measure dependencies are then analyzed recursively. The macro adds a “level” column to the table which displays the distance with the selected measure, and then filters the table to only show the related measures.
- List calculated columns: Creates a static list of all the calculated columns that exist the Power Pivot data model of the active workbook. Each calculated column is listed with its DAX expression and with the name of the table which it belongs to.
- List unused columns: Creates a static list of all the columns that exist the Power Pivot data model of the active workbook, highlighting those that are not used and providing info about where the others are used. Credits: Idan Cohen and Amir Hefetz.
- Model memory usage: Creates a table that analyses the size of each column that exist in the Power Pivot data model of the active workbook. Credits: Kasper De Jonge, Scott Senkeresty. NOTE: this feature works in Excel without requiring Analysis Services and provide a quick summary of the size of each column. You can obtain more detailed information with VertiPaq Analyzer, but you need to restore a Power Pivot model in Analysis Services in order to use it.
- Format DAX expressions: Uses daxformatter.com to create nicely formatted DAX expressions. You must select a cell in a table that contains a ‘DAX Expression’ column and the whole column will be formatted. Typically, you can use this button after you have created tables either with the “List calculated columns” or “List measures” buttons. It requires an active internet connection.
- Dax Studio: Opens DAX Studio without having to open another ribbon (by default, it is available in the Add-ins one).
You will find a more detailed documentation of other contextual menu features in the in the help file included in the ZIP.
- Added functions to list dependent or precedent measures of a selected measure.
- The DAX formatting feature now invokes the web service over https.
- Load the localization texts into a global variable at ribbon load time. This allows some contextual menus to be displayed without delay.
- Removed bug in procedure GetMemoryUsage of module PowerPivotDoc causing an error message in some cases.
- The DAX Studio button fails to launch the DAX Studio application in v2.7.0 and v2.7.1. It will be fixed from v2.7.2.
- Removed errors formatting a large number of DAX expressions (now it splits a large request in smaller batches).
- Localization in Spanish. (Credits to Vicente Castelló Ferrer.)
- Description field now added to the list of calculated columns.
- Option to extract and format measures and calculated columns DAX expressions in a single step.
- Option to automatically check for add-in updates at startup.
- New menu item to extract table source types.
- Automatic removal of ‘old’ contextual menus (v1.05)
- Localization of dialogs in English, French, German, Italian. Credits to:
- Dominik Petri for German
- Francesco Petrella, Marco Russo for Italian
- New “Insert PivotChart” button
- New command “delete unused columns”(XL2016 only – credits Idan Cohen and Amir Hefetz)
- Command “show connected slicers” added to the pivot table and pivot chart contextual menus
- Measure description column now added to the list of measures
- Picture “DAX Formatter credits” at bottom of DAX-formatted tables
- Ribbon buttons can now be accessed by keyboard shortcuts (Alt + PU + xx)
- Corrected bug in URL encoding of Dax expression before they are submitted to formatting API (credit Dominik Petri)
- Renaming of add-in file to avoid issue with Power Query. The add-in name does not contain the word “PowerPivot”
- Use custom UI XML for to call context menus (no longer use the “old” Commandbars-Object as suggested by Dominik Petri)
- Fixed issue on version checking function
- Corrected bug in increase/decrease decimal functions causing the unit label (kU,Mu) to be erased
- Renamed feature in “list unused columns” instead of “redundant”
- Added command to list redundant columns (credits Idan Cohen and Amir Hefetz)
- DAX formatting:
- Added option to format selected cells
- Added option to interrupt execution with ‘Esc’ key
- Performance improvement: directly read the color information from the html response (no longer paste html code to hidden worksheet).
- various bug corrections
- added command to check for updates of this add-in on www.SqlBI.com
- DAX Formatting insensitive to user country settings
- Pivot field number formatting also work with ordinary pivot table. Previously only pivottables connected to cubes were supported
- Added error trapping when user try to add data bars while a pivot field in rows or columns is selected.
- Memory Usage: the two DMV queries now use the same COLUMN_ID column
- Improved error handling, cleaned the code
- Corrected the calculation of Model Memory Usage (now aligned with script by Scott Senkeresty)