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:

  1. Leverage the power of DAX measures.

  2. Perform source data filtering and cleansing via Power Query.

  3. 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:

  1. 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.
  2. 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.
  3. Analyze the configuration of the underlying PivotTable.
  4. Create a new explicit DAX measure for each field in the Values area of the source PivotTable (including any Calculated Fields!).
  5. 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.
  6. Build a Data Model-backed version of the PivotTable or PivotChart on the new “dm-” version of the worksheet.
  7. Format the PivotTable as closely as possible to the original version.
  8. Create Data Model compliant versions of all slicers and timelines, and link them to the new PivotTables.

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.

Selecting PivotTables to upgrade in the Modern Pivot Monkey

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:

  1. Left-clicking a PivotTable or PivotChart directs Monkey Tools to select it for you, so you can see exactly which it is.
  2. 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:

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!



Related Posts


Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get


Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get


Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get


Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get


Article Overview One of the realities of building complex models is that we build models which take a long time to refresh. While timing with