An email from a client this morning underscored the need for a blog post that provides a Monkey Tools Roundup. With the amount of time and effort that went into Master Your Data, I sorely neglected the blog and specifically telling people about the new features that we’ve released this year. So let’s take a quick look at the highlights of what we added to this tool in 2021 (so far). And if you don’t have Monkey Tools yet, you can download a free trial here.
Monkey Tools Roundup - Summary Jan to Aug 2021
Monkey Tools automatically checks for updates every couple of weeks, and most of the time you’ll find that a new one is available for installation. Once thing we don’t do a very good job of, however, is telling you what’s in them. Often times they are just bug fixes, but they also include new features. Typically we’ll announce those during user group presentations with a demo, but not always.
Here’s a summary of the major releases we’ve released so far in 2021:
For reference, this isn’t all of the releases, just the biggest ones. There were about a dozen other releases scattered throughout this window as well. Those were mostly related to fixing bugs, but also included some internal work that we did in order to improve our Monkey’s brain, as well as provide us with the ability to incorporate Beta features to specific audiences.
Monkey Tools Roundup – Features Released
A big reason for the Monkey Tools Roundup is that we’ve been a bit remiss in telling people about these new features. So let’s look at the specific features that we’ve added to the tool so far in 2021. We’ll start with the oldest first…
Added Option flag for new PivotTable buttons
If you are on Office365, you may have received the new experience where the Insert -> PivotTable menu no longer contains “PivotTable” and “Recommended PivotTables”, but rather is split across three buttons (as shown in item 1 of the image below).
We love this new menu, and really wanted to incorporate those buttons on our Pivots & Filters menu as shown in item 3 above. Unfortunately, we have no way to programmatically identify if you have them or not, which meant that we have to serve a menu that looks like item 2. To solve this we added an option under Options -> Global Options -> Use Legacy PivotTable Menu Buttons. By default this is checked, returning the view shown in item 2. But if you have the left hand menu shown above, just uncheck it and you’ll have a menu that shows like item 3.
Query Monkey -> Create Query From M Code
There are many times where I copy a query and need to paste it into Power Query. The feature allows you to do this, but also allows indenting and choosing the load destination right up front. It was designed primarily to assist with copying and pasting samples when helping users in forums, but can also be a handy form if you have a library of queries that you maintain outside Monkey Tools.
The SCD2 Monkey
In our Dimensional Modeling course at Skillwave, we spend about an hour going through the manual process in order to solve the Type 2 Slowly Changing Dimension challenge. This feature allows you to solve the same goal in under one minute. It is fully configurable for keys with or without meaning. Granted, it does require some understanding of dimensional modeling, but for those who run into the issue, it’s sure to save you a TON of time.
If you’d like to understand the background of how (and why) this feature exists, you should watch the recording of Quickly Solving the SCD Challenge on my YouTube channel. The first 48 minutes show the manual way to solve the challenge. From minute 48 on you can see the demo of this tool in action.
Since the initial release of Monkey Tools, I’ve always had the goal of adding this feature. Import Monkey allows you to import your models from either a Monkey Tools backup file or – more impressively – Power BI Desktop files, recreating them in Excel. While there are things in Power BI that we can’t bring backwards, there is a great deal that we can recreate including queries, relationships and measures. And what we can’t build for you, we’ll report.
For a full demo of how the Import Monkey works – as well as an overview of a ton of other Monkey Tools features – check out the webcast that I did with Reid Havens.
Measure Monkey -> Filter Context Measure
One of my students asked for this during one of the AMA sessions of my Self Service BI Bootcamp. We thought it was a good idea as they were copying and pasting back and forth from other applications to make it happen. This feature basically allows you to create a measure to return the filter context of a given cell into a PivotTable. I can’t claim credit for the DAX pattern itself, but giving you a variety of options to create the measure is something that we’re proud of:
And after adding this measure to a PivotTable, it nicely shows the filter context at every given row/column intersection:
DAX Table Monkey
This feature was discussed and demonstrated in my Creating Unpivotable Charts presentation for the Vancouver Power BI and Modern Excel user group. (It makes its first appearance at approximately minute 48 of the presentation.) Basically, what it does is allow you to create tables in the Excel worksheet that retrieve values from the data model via DAX queries. While the meet-up recording does still mention that it is in Beta, it is fully release in the software.
Detailed Error Logging diagnostics
This is honestly something that I hope you’ll never need: in order to help debug issues with Monkey Tools, we’ve added a flag to turn on detailed error logging to a file for the current session. It can be turned on in the Global Options and is super helpful tracking down the source of bugs that people report to us so that we can fix them.
Insert a fnGetMonthEnds function
This new feature injects a handy little function which will return a table of the Month-Ends between two dates, as shown here:
This pattern is actually super helpful, especially if you need to allocate items over a given number of months: a technique that I’m going to be demonstrating in a session called “Several Ways Since Sunday” on August 29, 2021.
Monkey Tools Roundup – What We Are Working On
To finish off our roundup, I figured I should add a quick note as to what is coming up next. We’re not promising a lot of new features over the next couple of months. The reason for this is that we are actually doing a lot of internal work on the Monkey’s brain at the moment. We’re refactoring a lot of old code to make it more robust and scalable, which will allow us to add some other cool things that we are unable to accomplish currently.
Having said that, we do have some features in the pipeline as well, but it is a bit too early to share details about what they are. If you have ideas that you’d like to see in Monkey Tools, please do share them with us. You can do so via our Feature Request form.
I hope that this has helped enlighten you as to the new features that have arrived this year. We’ve still got a lot of stuff on our list, one of which is being a bit more pro-active about announcing what has happened!