Monkey Tools

Modern Pivot Monkey

Article Overview

The Modern Pivot Monkey’s goal is to help you quickly take a classic PivotTable and create an upgraded Data Model-backed PivotTable, opening the abilities to:

Leverage the power of DAX measures.
Perform source data filtering and cleansing via Power Query.
Expand the data model’s logic, enhancing the analytic output.

In this article, we’ll explore exactly what this Monkey does, as well as how to use it.

What the Modern Pivot Monkey does

Converting a classic PivotTable to a Power Pivot-based PivotTable actually takes a lot of work. First you have to make sure the data gets to the Data Model, and then check for any Calculated Fields that need to be re-created via the DAX formula language. After you cross those hurdles, you then have to rebuild your Pivots with the same layouts and options. All in all, it can take a good chunk of time. And that is where the Modern Pivot Monkey comes in – our Monkey takes care of this for you, so that you can focus on adding value.

When you trigger the Modern Pivot Monkey, it will take the following steps for each selected PivotTable and/or PivotChart:

Check to see if the data source is an official Excel table or a regular range. If a range, it will turn the data into a proper Excel table.
Check to see if the (new) Excel table is already loaded to the Data Model. If not, it will load the Table’s data to the Power Pivot data model.
Analyze the configuration of the underlying PivotTable.
Create a new explicit DAX measure for each field in the Values area of the source PivotTable (including any Calculated Fields!).
Check for the existence of a worksheet called “dm-” followed by the PivotTable’s source worksheet name and create it if it does not exist.
Build a Data Model-backed version of the PivotTable or PivotChart on the new “dm-” version of the worksheet.
Format the PivotTable as closely as possible to the original version.
Create Data Model compliant versions of all slicers and timelines, and link them to the new PivotTables.

NOTE The Modern Pivot Monkey creates copies of your Pivots, it does not delete the old ones. We do this to allow you to verify that everything is working as intended before you remove any existing business logic. This will allow you to also copy things that we do not deal with, including conditional formatting, non-PivotTable data, and formulas on the worksheets.

Working with the Modern Pivot Monkey

Getting Started

The Modern Pivot Monkey can be found on the Monkey Tools Pivots & Filters menu. When launched, it will quickly scan your workbook and present you with a list of all classic (non-Data Model) PivotTables and PivotCharts in the workbook.

You have the ability at this point to:

Click “Modernize Selected Pivots” to upgrade all classic PivotTables and PivotCharts.
Select one or more (via holding down the CTRL key) PivotTables and/or PivotCharts to upgrade.

Upon clicking “Modernize Selected Pivots”, the Monkey will go to work for you.

Hidden User Interface Features

We know that a lot of users don’t bother renaming their PivotTables. This means that the report can be a bit overwhelming and non-descriptive at first glance. For this reason, there are a couple of hidden features that can prove quite useful:

Left-clicking a PivotTable or PivotChart directs Monkey Tools to select it for you, so you can see exactly which it is.
Right-clicking an entry will allow you to rename it to something more logical. (And don’t worry, the new version will have the same name, prefaced with “dm-” to keep them straight.)

Note that renaming your PivotTables will rename them in Excel, not just in the Modern Pivot Monkey.

How Close are the Modernized Versions to the Original?

For the most part, you’ll find that they are pretty close. If you tend to do particularly gnarly things with your PivotTables or configure them in odd ways though, you may need to replicate some of those items on the new PivotTables.

We do attempt to port:

PivotTable data
PivotTable field choices
PivotTable layouts
PivotTable total/subtotal positioning
Formatting (except conditional formats)
PivotTable options
Slicers and timelines

We do NOT attempt to port/update:

Conditional formatting rules on the PivotTables
GETPIVOTDATA formulas
Anything other than the actual PivotTables, PivotCharts, linked timelines, and slicers
Slicers or timelines that are not on a sheet hosting a PivotTable

Conversion Examples

Shown below are a few screenshots of PivotTables that have been converted via the Modern Pivot Monkey:

A modernized version of a classic PivotTable looking very close to the original

Side by side image of the classic and modernized PivotTable options

Modernized version of the PivotChart, which looks almost identical to the original version

Our Motto: Trust by Verify If you are making fairly standard configuration changes to your PivotTables (layout, formatting, and general option settings), our Monkeys are highly likely to give you a Modern Pivot which matches your original. If, however, you are getting deep into field customization, you may find they miss things. Always verify your outputs before deleting your original PivotTables.

See the Modern Pivot Monkey in Action!

Would you like to see the feature in action? Have a look at the video which Ken introduces the feature and gives a demo as to how it works!

Monkey Tools

Modern Pivot Monkey

Article Overview

What the Modern Pivot Monkey does

Working with the Modern Pivot Monkey

Getting Started

Hidden User Interface Features

How Close are the Modernized Versions to the Original?

We do attempt to port:

We do NOT attempt to port/update:

Conversion Examples

See the Modern Pivot Monkey in Action!

Share:

Related Posts

DMVSleuth

ModelSleuth

DAXSleuth

PivotSleuth

TimeSleuth