We are super excited to announce that we’ve (at last) released the first version of our Monkey Tools software! Ken has been working on this software on and off for the better part of 8 years now. But after showing it to a friend in Wellington last year, we decided it was finally time to get serious. We hired a full-time developer last summer and are finally ready to go live with the initial release!
NOTE: Since this post was originally posted, Monkey Tools has really grown up. There are many more features than those listed below, and it has it's own website. To find current information on Monkey Tools, please visit https://monkeytools.ca
What is Monkey Tools?
Monkey Tools is an Excel add-in (supported in Excel 2016 and higher) which provides tools for you - as a business intelligence author/reviewer - to:
- Build models more rapidly
- Follow recommended practices
- Document your work
- Audit files that you receive
It is targeted primarily at modelers and analysts who work primarily in Excel, but also push their models into Power BI. (Our philosophy at Excelguru is to model in Excel first, then export to Power BI for reporting, sharing and security control.)
Oh, and super important… it installs on your system without requiring admin rights on your PC. How cool is that?
What does Monkey Tools actually do?
Well… lots! We’ve collected all the cool features under some themed buttons including:
- QueryMonkey (for inserting new queries)
- DestinationSleuth (to provide information on query load destinations)
- QuerySleuth (helping understand your actual queries)
- TimeSleuth (to benchmark query load times)
- PivotSleuth (helping you diagnose Pivot Table field issues)
- DAXSleuth (tools especially for working with DAX measures)
- ModelSleuth (reporting on the properties of your queries and data model)
Cute names, right? The Monkey builds things, and the Sleuths investigate things. Here’s a high-level view of what they each contain.
QueryMonkey
Query Monkey gives you the ability to insert key queries like:
- The famous “fnGetParameter” query and table (from Chapter 24 of M is for (Data) Monkey)
- A “From Folder” setup that works with local and/or SharePoint hosted files
- Dynamic calendar tables based on your data (for custom calendars, it even provides the option to insert the "periodicity" columns for Rob Collie's GFITW DAX pattern!)
DestinationSleuth
Today, this is simply a viewer to visually indicate the load destinations of your tables (better than just “Connection Only” or “x Rows Loaded”).
QuerySleuth
This is a single form, packed with information and features such as:
- A dependency/precedent tree view layout
- Full colour display based on load destination
- Colourful and indented M code
- The ability to modify the M code and write it back to the editor WITHOUT LOCKING YOUR EXCEL User Interface!
TimeSleuth
This feature allows you to time query execution in Excel, and even chart comparisons between them with or without privacy settings enabled. If you’ve ever wondered which query is slowing down your workbook, or wanted to time test two different approaches, you may find this helpful!
PivotSleuth
Have you ever seen that irritating “relationships may be needed” error when building a Power Pivot based Pivot Table, and wondered why? Pivot Sleuth can tell you…
- See the real, fully qualified names of the fields used in your Pivot Tables
- Highlight potential or current issues in Pivot Table configurations
- Debug cross filtering issues, “relationships may be needed” errors and errors where grand totals are returned for all rows on the Pivot Table
DAXSleuth
We believe that measure dependencies are just as important as query dependencies, and this is the reason we build the DAXSleuth. This form:
- Displays a dependency/precedent treeview of your DAX measures
- Provides a full colour display of Implicit and Explicit measures (with or without children), as well as Calculated Columns
- Shows your DAX measures with colour highlighting in an indented format
- Allows you to Indent, Un-Indent, Flatten, Duplicate and even Update measures without leaving the DAXSleuth
- Exposes all locations a DAX Measure has been used (Pivot Tables, Pivot Charts, OLAP Formulae and Named Ranges), and even allows you to select those objects right from the DAX Sleuth!
ModelSleuth
Have you ever had to provide documentation for your model? Or picked up a model from someone else and had to review it? The ModelSleuth provides reports and utilities such as:
- A full model summary report showing key statistics about your tables, relationships, columns, measures and queries. (Trial and Free licenses are limited to every other record in this report.)
- A model memory usage report, complete with how much memory is recoverable (for Excel based data models).
- An unused columns report (for Excel based data models).
- A DMV Explorer (for those who want to build their own reports).
Monkey Tools Supported File Types
The Monkey Tools add-in is compatible with Excel 2016 or higher, and can read from:
- Excel files
- Power BI Desktop files
- Backup files (that you can export from the Monkey Tools software)
Will Monkey Tools get updates?
Oh yes, we have plans for many more features!
Our intended model is to deliver features (and bug fixes) as we develop them. That means that there could be periods with no updates as we work on something big, or periods with multiple updates delivered in a single week. We know that some people love frequent updates and some people don’t, so we let you control how often you get them:
The key thing to recognize here is that we are not holding new features for a vNext. They’ll be delivered when they’re ready.
Can I try Monkey Tools before I buy it?
Ken did not become or remain a Microsoft MVP without contributing a large portion of tools and help to the community for free, and that won’t change. Having said that, we’re paying a developer to work on this product full time and need to recoup those costs. For that reason, we will always have both a Free version, as well as a Pro version.
Naturally, we want you to look at it, as we're convinced you'll like it. And that's why we have a two-week trial that provides full access to almost all of the full feature set. Once your trial expires, your license will automatically revert to a free license. You’ll still get fixes and new features, they’ll just render in free mode (without colour, without field advice, etc.). We do believe that you’ll still find the tool useful, just maybe not as useful without a Pro license.
Ready to learn more about pricing options and download the free trial? Click here to go to the official MonkeyTools website!
10 thoughts on “Monkey Tools is Here”
Wow, this looks amazing!
Any plans to offer Power Query Academy members a discount on this and future tools?
Just curious
Hi Nick,
At this time we're going to keep this as an individual product that is sold through Excelguru, and not something that will be provided as part of the Academy benefits. While I don't have plans to change that in the near term, I appreciate the question, and may think about it at a later date.
Pingback: Monkey Tools Update Now Available - The Excelguru BlogThe Excelguru Blog
Hi Ken,
I followed instructions for fnSmartFolder and invoked it. The target folder has a couple of CSV files. When I try to combine them in the generated query, I get the infamous:
Formula.Firewall: Query 'Invoked Function' (step 'Expanded Table Column1') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
The documentation states that fnSmartFolder avoids this firewall, unless it's the query itself that's in violation?
At any rate, I recall seeing a workaround for this. Here is the M query. What needs to change?
let
Source = fnSmartFolder("C:\Users\...\DLA US"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
#"Expanded Table Column1"
Cheers,
Mitch
Hi Mitchell,
Ah, good catch. So as it turns out, you need to separate your queries in order to bypass this. (The formula firewall is such a pain.)
To fix this:
1) Rename your query to what you want it to be called
2) Right click the "Filtered Hidden Files 1" step and choose Extract Previous
3) Enter the query's original name
This will separate the Source step into it's own query, and then you should be good to go.
Here's how mine looks:
FilesList:
letFolder = fnGetParameter("File Path"),
Source = fnSmartFolder(Folder)
in
Source
And then my "Data" query:
letSource = FilesList,
#"Filtered Rows" = Table.SelectRows(Folder, each ([Extension] = ".txt")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
#"Expanded Table Column1"
Pingback: Monkey Tools Update jetzt verfügbar | Anfänger Kurse
This is nuts! I had no idea how powerful this add-in was.
You should check out the current features Richard. Monkey Tools has grown a lot since this was posted over 3 years ago. Monkey Tools now has its own home on the internet at https://monkeytools.ca, and an ever-growing knowledge base of how all the features work. 🙂
Pingback: Monkey Tools Update Now Available - Excelguru