Update to Monkey Tools QuerySleuth

We've been kind of quiet here, but we're excited to announce that we've just published an update to Monkey Tools QuerySleuth feature.  It now contains an "tabbed" experience so that you can easily flip back and forth between queries, "pinning" the ones you want to see and compare.

The Updated QuerySleuth Interface

In this case you'll notice that I pinned The ChitDetails and ChitHeaders queries, then selected the Locations query from the left menu.

An image of the update to Monkey Tools QuerySleuth showing the new tabbed interface indicating two pinned queries and two modified queries

Why does this matter?  Did you notice that the ChitDetails and Locations tab names are both red?  That's because I made changes to both of them to update a data type... I can now hold onto those changes as I flip back and forth between JUST the queries I want to keep in focus.

Updating Multiple Queries

But now, of course, I want to commit my changes and force the data model to update to reflect those changes.  In this image, I'm doing just that, with three queries:

An image of the QuerySleuth prompting the user to ask which queries they want to save and refresh

And due to the selection pointed out by the arrow, each of these queries will not only get saved back to the Power Query engine, but a refresh of each query will be triggered as well.

So how do you get this update to Monkey Tools QuerySleuth?

This update to Monkey Tools QuerySleuth is available in Monkey Tools 1.0.7553.5975 or higher.  And it's available in both the free and Pro versions of the tool.  (Of course, you will still need a Pro version in order to actually save your queries.)

To try our free trial, head over to the Monkey Tools product page to download your copy.

If you already have Monkey Tools installed, it will automatically update within a couple of weeks, or you can request the update now by going to Monkey Tools -> Options -> Check For Update Now…

Creating Power Query Based Calendars

We’re super excited to announce that we have given the Monkey Tools calendar creator feature an upgrade.  In fact, it is so much of a power up, that this feature has graduated into its own full grown monkey!  We call it the Calendar Monkey.

In our initial version, the Calendar Creator would create the queries necessary to load a single column calendar of unique dates into the data model and add PeriodID columns for 364-day calendars like 445 and 13 fiscal periods.

The Calendar Monkey adds a couple of pretty powerful features to this original mix.  Let’s walk through the experience quickly to see what I mean.

Step 1:  Define the Calendar Boundaries

Step 1 screen of the Calendar Monkey, allowing you to choose your calendar type, the calendar start/end boundaries and year end

Not much has really changed here.  You can still:

  • Pick your calendar type (12 month, 13 month, 445, 454, 544), and define your custom year end (including a different month for 12 month calendars.)
  • Define a name for your calendar table query
  • Choose the load destination
  • Pick any valid date columns for the Start and End date of your data

These settings allow our monkey to build the calendar to dynamically span the entire range of your data on every refresh.

The only real difference here is that we’ve added a checkbox and a Next button.  (Notice that Create is still available, if you just want to accept the monkey’s default choices for the rest of the options you’re about to see.)

Step 2:  Choose Calendar Columns

One of the things that always bothered us about our original version is that it created the calendar’s Date column, but then left it up to you to add the different date formats that you wanted.  So we decided to improve that, as you can see here:

Step 2 of the Calendar Monkey screen provides a series of check boxes allowing you to specify which date format columns you'd like to add to your model

Our monkey pre-selects the most common date formats, but if you ever check/uncheck one, it will learn your preferred defaults and provide those next time you go to inject a calendar.  The columns shown dynamically react to your choices in Step 1 as well… if you use a Dec 31 year end for a 12 month calendar, you only need the first two columns – so that’s what the Calendar Monkey will show you.  If you have a custom year end (like Jun 30 or Sep 30), you may also want Fiscal columns, so the monkey provides those as options too.  And if you work with a 364-day calendar like a 445 variant or a 13 fiscal periods calendar… there is a final column of PeriodID’s that shows up in that blank spot too.

Step 3: Adding Relationships

Depending on your choices in Step 1, the Calendar Monkey will determine if you will be presented with this page or if it will be skipped.  If you choose to load your calendar to the Data Model (or Data Model & Worksheet), the monkey will list every date column loaded to the data model.  The purpose of this is simple; let you decide if your new calendar table should be linked to any of those columns listed.

For any columns you check, Calendar Monkey will do its best to create those relationships after loading your table to the data model.  (There are some things that can prevent the monkey from accomplishing this, such as creating an inactive relationship.)

Step 3 of the Calendar Monkey provides checkboxes for each date column in the data model, allowing you to declare which tables you'd like to relate your calendar table to.

Step 4: Creating the Calendar

Even though the Calendar Monkey has a lot of work to do when you click create, it also knows the value of good feedback.  For this reason, it will update you as to the progress as it completes all the individual tasks, as you can see here:

This screen shows feedback from the Calendar Monkey, letting you know what has been done, as well as what you need to do need.

Now unfortunately, there are a couple of things that the monkey is unable to do (thanks to a lack of security clearance with the Excel data model).  Rather than just ignore these essential tasks, however, it will tell you what needs to be done, with the exact steps to do so.

As a bit of a pro-tip here… you don’t actually need to close the Calendar Monkey window to take action on those steps… so keep it open until you’ve hopped into the data model and made the advised changes!  Of course, if you understand what’s happening, and know the steps you’ll need to take, there is also an option to automatically close the summary screen upon completion as well.

Looking at the data model with the new calendar table already related to the other tables

What the Calendar Monkey cannot do

There are three things that the Calendar Monkey can’t do at this time:

  1. Automatically hide the keys on the “many” side of the relationship (the foreign keys)
  2. Automatically create the sorting hierarchies to sort Month Name by Month Number and Day Name by Weekday Number
  3. Create the calendar in a Power BI file

Honestly, while we joked earlier that the Calendar Monkey doesn’t have security clearance, the reality is that there is a limitation with the data model’s extensibility model which is preventing us from solving the first two items.  The third… its on our backlog.

So how do you get the new Calendar Monkey?

You need Monkey Tools version 1.0.7493.29574 or higher, and you'll have the Calendar Monkey ready to do your bidding.

If you haven’t already, head over to the Monkey Tools product page to download a copy

If you already have Monkey Tools installed, it will automatically update within a couple of weeks, or you can request the update now by going to Monkey Tools -> Options -> Check For Update Now…

Name Worksheets After Queries

Have you ever loaded a Power Query to a worksheet and then changed the name to match the query? It's a shame that there is no option to name worksheets after queries, as this would be handy.

Well, after seeing this request come up in the forums last week, we thought that this would be a great feature to add to the DestinationSleuth in Monkey Tools.  So as of build 1.0.7433.38066... it's done!

How to Name Worksheets After Queries

We wish that we could add this as an option in the Close & Load dialog, but sadly that's not possible.  So we did the next best thing...  Once you've loaded your queries, you simply need to open our DestinationSleuth and:

  1. Select the queries you're after
  2. Click the Rename Sheets button

Using DestinationSleuth to select queries and change the names of their host worksheets

At that point, we'll quickly loop through the host worksheets and rename them to match the query landed to that sheet.

Name Worksheets After Queries While Changing Load Destinations

You might also notice a new checkbox called "Name Sheets After Queries".  This checkbox allows you to name worksheets after queries while changing a load destination to create a new table.  It's also super easy to use:

  1. Select the query (or queries) you wish to change
  2. Choose to change the load destination to a Table
  3. Check the Name Sheets After Queries checkbox
  4. Click the Update Load Destinations button

Using DestinationSleuth to change a load destination from Connection Only to Table, and update the worksheet to the Query name at the same time.

We'll change the Load Destinations, creating the worksheets AND naming them to match the query in one step.

This is also a "Forever Free" Feature

As mentioned in my last post, while the DestinationSleuth's colour highlighting is only available in the trial and pro versions of Monkey Tools, the ability to Change Multiple Load Destinations at Once is a "forever free" feature.  And so is the ability to rename worksheets after queries!  All you need is Monkey Tools version 1.0.7433.38066 or higher, and you'll have that ability at your disposal.

If you haven’t already, head over to the Monkey Tools product page to download a copy

If you already have Monkey Tools installed, it will automatically update within a couple of weeks, or you can request the update now by going to Monkey Tools -> Options -> Check For Update Now…

 

 

Change Multiple Load Destinations at Once

Have you ever built multiple queries in one Power Query session?  You get to choose one load destination, then have to change each of the incorrect ones... one by one.  Have you ever wished you could change multiple load destinations at once?  Well now you can - you just need Monkey Tools!

Check out the New and Improved DestinationSleuth!

Following on the heels of last week's feature updates, we've added functionality to the DestinationSleuth that allows you to change load destinations of any one or more queries.

Naturally, DestinationSleuth still gives you a very visual view of the different query load destinations all in one place, but do you see that new option just to the left of the Exit button?

DestinationSleuth showing the different load destinations in colour

Assume that you've created 5 new queries in the workbook, and load them all to Connection Only.  But now you want to change 4 of them to the data model.  Rather than click each query, change the load destination, and wait, then move to the next one... Now you can just launch DestinationSleuth and:

  1. Select the load destination
  2. Select each of the queries you want to repoint (hold down your CTRL key)
  3. Click Change

DestinationSleuth being using to change multiple load destinations at once

Sure, it still takes time, but at least you can walk away and let it cook, rather than slowly shepherd it through the entire process.

Delete Host Worksheets

You'll also notice a little checkbox called "Delete Host Worksheets".  You know that issue where you accidentally load a table to the worksheet, then change the query to Connection Only?  It leaves the worksheet behind.  Now true, you can always just delete the worksheet (which will actually set the query to Connection Only automatically). But what if you need to change 5 of them?  It's easy with DestinationSleuth:

  1. Select the load destination
  2. Check the "Delete Host Worksheets"
  3. Select each of the queries you want to repoint (hold down your CTRL key)
  4. Click Change

We will repoint your queries AND remove the worksheets that were holding the query results.  (Naturally, you want to be really sure you acutally want to do this, but it's handy if you do.)

This is a "Forever Free" Feature

While the DestinationSleuth's colour highlighting is only available in the trial and pro versions of Monkey Tools, the ability to Change Multiple Load Destinations at Once is a "forever free" feature.  All you need is Monkey Tools version 1.0.7423.41125 or higher, and you'll have that ability at your disposal.

If you haven’t already, head over to the Monkey Tools product page to download a copy

If you already have Monkey Tools installed, it will automatically update within a couple of weeks, or you can request the update now by going to Monkey Tools -> Options -> Check For Update Now…

Monkey Tools Update Now Available

We’re pleased to announce the first Monkey Tools update is now available for download! This one contains a new feature, some new logic and an update to one of our data connectors. Read on for more information!

I can’t believe it’s been a month since our initial release, but here we are.  Since that release, a couple of notable things happened:

  • We published a blog post on PivotSleuth and Measure Tables. This was a learning experience for me, as I discovered something new about Measure Tables.  I always knew that you could mark your Fact tables as Measure tables by hiding all the unaggregated columns, but I didn’t realize that this also means that disconnected Measure tables will then suppress the “Relationships between tables may be needed error.”
  • One of my friends hit me with an interesting curve ball: he turned on “Store datasets using enhanced metadata format” in the Power BI preview features. And as it turned out, some of the methods we’d been using to analyze the Power BI model disappear when you do that.

Both of these have led to some improvements in the software, which we’re proud to say are finally available to you.

What’s new in version 1.0.7418.29970?

There are three major things that are new in this version:

A New Power BI Connector

This was actually a huge amount of work for us, as not only did we have to build a new connector to read the new Power BI file format, but we also had to analyze the file as it was opening to see if it was in the classic format or not.  And to make it harder, if you have the Enhanced Metadata Format turned on, a legacy file requires using our initial connector, but any refresh must be done with the new connector.  Fun times for a developer and, as you can imagine, it took as a bit of effort to pull it off.  It’s actually this piece that has held us back on the other features, as the connector MUST work and impacted everything.

While most of this work is invisible to the end user, there are two things that hope you do notice:

  • Connecting to the new file format is much faster than using the legacy format.
  • We also took the time to remove the reliance on configuring the version of Power BI that launches for you by default. We now just launch Power BI using your default connector, then bind to it, no matter how many versions you have on your machine, or which they are.

Updated Functionality in the PivotSleuth

As we learned in the PivotSleuth and Measure Tables blog post, hiding all unaggregated columns on a disconnected measure table flags the table as an official Measure table and suppresses the “Relationships between tables may be needed” message.  For that reason, PivotSleuth needed to recognize that this is acceptable.

In other words, when the Measures table is a properly formatted disconnected table, it needed to (and now does) show that there are no issues:

PivotSleuth giving a clean bill of health for a measures from a disconnected measure table

But when that disconnected Measures table has a visible column, not only should it have shown the issues, but also tell you what needs to be done to fix them.  And now it does:

PivotSleuth showing issues for measures used from an improperly formatted measure table

New QueryMonkey Feature: Add Measure Table

As mentioned before, our philosophy is “Build better, faster”.  For that reason we’ve added a new QueryMonkey feature for you:  Add Measure Table.

This feature will prompt you for a name for the table…

Prompting the user to enter a name for the new Measures table

And then create a new empty table in the data model for you:

An empty table in the data model

Unfortunately, the Excel team hasn’t given us a way to programmatically hide columns in the data model (we could REALLY use that ability), so we can’t take that last critical step for you:  Hiding the Measures column to prevent the "Relationships between tables may be needed" message.  But never fear, we do tell you exactly what needs to be done:

Advice from PivotSleuth on what to do to turn the new table into a proper measure table

So while we typically store our measures on the Fact tables, rather than a disconnected Measure table, we totally get that a lot of people like this approach.  Hopefully this make it a lot easier for you!

How do I get the Monkey Tools Update?

The answer to this depends on whether or not you’ve installed Monkey Tools yet.

If you haven’t, then head over to the Monkey Tools product page to download a copy

If you already have Monkey Tools installed, it will automatically update within a couple of weeks, or you can request the update now by going to Monkey Tools -> Options -> Check For Update Now…

Happy sleuthing! 🙂

Appending Columns with Different Names

Will Thompson from the Power BI team threw out a question on Twitter today related to Appending Columns with Different Names:

Let's say I have some CSVs coming in where the column names differ from file to file, but always in the same order. I could use PQ's automatic Column1/Column2 etc. names by skipping the header row and then have them all map to the same columns in AS.  Then build visuals on top of it that'll pick up the fixed column names from AS. Has anyone written a blog or tutorial that covers that sort of scenario? I want to point someone to some guidance…

No worries, Will, I’ve got your back. 🙂

Setting the stage

To be fair, the data source (CSV, AS, Excel or whatever), really isn’t relevant.  It’s all about the process of Appending Columns with Different Names.  What we need to recognize is that we need two types of tables here:

  • Data Tables: These tables have the data, but have different headers.
  • Header Table: This table has the correct headers for the data

And we also need to remember that the Data Table and Header Table columns are always in the same order.

Step 1:  Prep the “Data” tables

Preparing the data tables is pretty easy.  If the different column names are already showing in the headers like this:

A data table with headers

Demote and remove them by going to:

  • Home -> Use First Row as Headers -> Use Headers as First Row.
  • Remove Rows -> Remove Top Rows -> 1

Your table should now have headers called Column1, Column2, etc…

A data table with headers demoted into the first row

Do this for each table, then set each to be a staging query:

  • Excel: Load the query as a Connection Only query
  • PowerBI: Disable the Load of the query

Step 2: Prep the Header Table

Connect to your data, which might look like this:

A data table with headers

Clean it up by going to:

  • Home -> Use Headers as First Row -> Use First Row as Headers.
  • Keep Rows -> Keep Top Rows -> 1

You should now have a 1 row query that shows the names of the column headers.

A single row table with headers demoted to be the only row

It's time to save it:

  • Rename the Query as “Headers”
  • Set it to load as a staging query (as above)

Step 3: Combine the Data

This part is simple:

  • Right click the Header table -> Reference
  • Go to Home -> Append Tables -> Three or more tables
  • Add each of the data tables and click OK
  • Go to Use First Row as Headers
  • Set your data types

And you’re done:

A complete data table with the correct headers

Sample file (Excel) is available here.  But you can import it to Power BI if you prefer that look.

PivotSleuth and Measure Tables

Last week I got an email from one of our readers, with some feedback related to how Monkey Tools’ PivotSleuth works with Measure tables in Power Pivot.

Best practices is to set up a disconnected table to house DAX measures.  Because of this - all of the fields listed in the Pivot Sleuth end up being RED.  So it is not really useful.   Is there away around this?  Other than incorporating your DAX measures in an actual table?

And is this the reason for the " You might need a relationship" annoyance from Excel?

Personally, I challenge the term “best practices” here, as I think it’s borne a bit out of history, and no longer relevant.  But more on that below...

My favourite part about this email was the last line, as this specific problem is actually one of the main reasons we wrote this feature.  Let’s take a look and see what PivotSleuth says about this…

Model Background

The model framework I’m using looks like this:

Framework of a sample data model

As you can see, we have a separate _Measures table in this instance, where all the measures are collected, rather than storing the measures on the Sales & Budget tables.  They’re not complex at all, in fact, they’re about the easiest measures you could create:

  • Sales $: =SUM( Sales[Amount] )
  • Budget $: =SUM( Budgets[Amount] )

And when you drag them on to a PivotTable, they work nicely:

PivotTable using the measures we created

So, if everything is fine, what’s the issue?

It’s all about this nagging little message:

The annoying "Relationship May Be Needed" error

Why is it here?

PivotSleuth and Measure Tables

When you launch PivotSleuth against this PivotTable, you see the following:

PivotSleuth and Measure Tables showing highlighted fields

Why are all the fields red?  The answer is shown when you select one of the measures:  there is no relationship between the Sales, Categories, or Calendar tables.

So, what happens when you store these measures on the Sales and Budget tables?  The irritating message goes away:

Updated PivotTable where the measures are stored on the Sales and Budget tables

(Interested in the other things Monkey Tools does?  Check it out here!)

Why do People Create Measure Tables Anyway?

The practice of storing measures on another table as a recommended practice was born out of Power Pivot instability, back when things crashed a ton.  Sometimes the fix would require removing the table from the data model and re-creating it, at which point you’d lose any measures or relationships built on those tables.  It was frustrating and annoying, and led people to keeping their measures into a separate table to protect themselves from having to do that work.  The challenge, however, was that it caused a “Relationships May be Needed” message every single time you used a measure.  And there was no way to make that go away.

Since 2016, Microsoft focused on fixing bugs related to Power Pivot, with many of them making their way back into the Excel 2016 product, even if they were fixed after 2019 was released.  While they’re certainly not all gone, it’s unusual to see issues that force the need for tables to be deleted and rebuilt now.  To me, this reason for separating your DAX has basically become a non-issue, but the habit still remains.

Some people also argue that this gives you a central place to go to get your measures.  I’d argue that the list can become overwhelming when all your measures are in one folder without any categorization.  (Unlike Power BI, we can’t group measures into folders.)

I far prefer to put my measures on the appropriate tables, then hide all the unaggregated columns on the table.  This offers three benefits:

  1. It groups the measures by table, making them easier to find.  (Sure, you can use the search function, if you like. I’m just saying those tables act like folders.)
  2. It means the “Relationships May be Needed” warning only shows up when a measure can’t be cross filtered by a natural relationship in the model.
  3. It changes the icon of the table to the sigma icon (?), which is synonymous with measures.

The Benefits of Hidden Columns

Let’s take a quick look at the benefits of hiding every unaggregated column in the data model, essentially leaving only measures visible on your table.  This is super easy to do: you just hop into the Power Pivot window, right click every natural column and choose “Hide from Client Tools”.  (I personally prefer to do this in Diagram view, but it works in table view as well.)  The results are pretty impactful when you look at the PivotTable field list:

The results of hiding every unaggregated column

Notice that the tables are now flagged as “Measure Tables”.  They inherit the sigma icon, as well as move to the top of the list.  This is the primary reason I prefer to work this way… the measures are grouped logically based on their tables.  Essentially, they act like Measure Folders.

Full Disclosure on PivotSleuth

As I was writing up this post, I discovered something that I hadn’t tripped on yet.  Look what happens when I hide all unaggregated measures on a Measure table:

Measure table with all unaggregated measures hidden

Notice that there is no “Relationships May be Needed” message.  I didn’t realize that this flag would change that, and as of today Monkey Tools doesn’t check for this, so still shows all read.  So, it looks like we need to update our logic a bit to add some more functionality.  🙂

Conclusion

Hopefully we both learned something here!

Personally, I’m sticking with the individual table approach, and storing my measures as close to the table they summarize.  I like the ability that it categorizes my measures.  But ultimately, it’s cool that we have the ability to work the way we want to work.

And we’ll look at modifying Monkey Tools to avoid showing red when – and only when – you’ve hidden every unaggregated column on your measure table.

PS:  Did you know that you can’t call a table “Measures”?  It’s a reserved word, so will give you a table called “A”.  That’s why I went with “_Measures”

Monkey Tools is Here

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!

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!)

The QueryMonkey provides a Dynamic Calendar generator

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”).

The DestinationSleuth user form displays four different load destination types

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!

The QuerySleuth shows a query dependency tree as well as indented and colourful M code

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!

A chart generated by Monkey Tools TimeSleuth user form

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

Debugging PivotTable errors with the PivotSleuth

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!

Monkey Tools DAXSleuth user form in action

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).

Showing the impact of unused columns on memory via Monkey Tools ModelSleuth feature

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:

Monkey Tools allows you to control update frequency

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!

Unpivot Stacked Sets with Inconsistent Rows

I'm currently hanging out in New Zealand, with a friend who has generously let me stay at his place instead of a hotel.  What I didn't know when he offered a bed though, was that the cost of admission was a solution for a gnarly Power Query issue he was facing: How to unpivot stacked sets with inconsistent rows.

The data Jeff provided me with looked similar to this:

3 sets of data with products on rows and dates on columns

If it were only the first two tables that we were facing, this wouldn't be too difficult.  We cover unpivoting stacked data sets in both our Power Query Academy and our Power Query Recipes, whether they are single or multi column.  But the killer here is the third table... it has more rows than the first two.  So the question becomes how do we unpivot stacked sets with inconsistent rows?

Preparing the Data

Obviously the first piece we need to do is to get the data into Power Query and remove the irrelevant rows and columns.  The steps I went through were these:

  • Pull the data in to Power Query
  • Filter Column1 to remove null values
  • Remove the Total column

Once done, my data looked like this:

Stacked Pivoted Data Sets with 3, 3 and 4 rows

So now the data is nice and clean, but how to you unpivot it?

Separating the Data

The first trick is to find a way to separate this data into individual tables that represent each of the stacked data sets.  The good news here is that there is an indicator that we are looking at a new table.  Every time we see "Products" in Column 1, we know that's a header row and a new table will begin.  So we'll use this to separate the data into blocks, starting a new block each time we see that term.  To do this:

  • Go to Add Column --> Index Column --> From 1
  • Go to Add Column --> Conditional Column and configure it as follows:
    • Name:  Set
    • Formula:  if [Column1] = Products then [Index] else null

Shown below is the image view of the Conditional Column, as well as the results that it will create:

Building a Set column returning the Column1 if Column1 equals Products or null

As you can see, we've pulled out the number from the Index column if - and only if - the value in the first column is "Products".  The reason we want the null is that we can then:

  • Right click the [Set] column --> Fill Down
  • Select the [Index] column --> press DEL

You're now left with a nice table where the Set column shows a unique value for each data group:

Stacked Data with a "Set" column showing a unique value for each set

Grouping Into Tables

With an indicator for each group of data, we can now leverage this to separate the data into the individual data sets.  The method to do this is Grouping.

Select the Set column and then:

  • Go to Transform --> Group By and configure as follows:
    • Group by: Set
    • New Column Name: Stage1
    • Operation: All Rows

Grouping the Set column and adding an aggregation called Stage1 for All Rows

The data will then be grouped by the values in the Set column, and show the original data that was used to generate those groups.  Clicking in the whitespace beside the Table keyword will show each of these rows that were used in the grouping for that data point:

Results of the Grouped table, shown by clicking in the whitespace next to a group

Cleaning up the Grouped Tables

The challenge we have here is that we want to unpivot the data, but we've got some extra data here that will pollute the set: the values in the "Set" column which were added to allow the grouping.  We need to remove that.  To do so:

  • Go to Add Column --> Custom Column and configure it as follows:
    • Name:  Stage2
    • Formula: =Table.RemoveColumns( [Stage1], "Set"

Compare the results to that of the Stage1 column:

The Stage2 data table looks like the Stage1 data table, except the Set column has been removed

Before we can unpivot data, we need to promote that first row to headers... but we need to do it for each column.  No problem, we'll just break out another custom column:

  • Go to Add Column --> Custom Column and configure it as follows:
    • Name:  Stage3
    • Formula: =Table.PromoteHeaders( [Stage2], [PromoteAllScalars=true] )

Wait... what?  How do you figure that out?  I cheated.  I grabbed another table, promoted headers, then looked in the formula bar to figure out the syntax I needed.  The function name and table name were pretty obvious but unfortunately - even with intellisense - that final PromoteAllScalars part doesn't auto-complete.  Even worse, if you don't included it, it essentially just eats the top one row.  Once I had it correct, the results are exactly what I needed:

The Stage3 table now shows the headers promoted

As you can see in the image below, the Stage 3 table contains columns that have headers, as we wanted.  The 3rd table (carrying the identifier of Set 9), shows four rows, while the other tables show 3 rows.  So the data is now separated into tables, but they still have an inconsistent number of rows.

The Set1 group has 3 rows, and Set9 has 4 rows

Unpivot the Data

We have done everything we need to do in order Unpivot Stacked Sets with Inconsistent Rows.  We now just need to unpivot the data.  So let's do it:

  • Go to Add Column --> Custom Column and configure it as follows:
    • Name:  Stage4
    • Formula: =Table.UnpivotOtherColumns( [Stage3], {"Products"}, "Date", "Units" )

An indented version of the formula, as well as the results it produces, is shown here:

Displaying the Unpivot formula and the results for Set1

How do you learn to write this?  Click on one of tables to drill in to it, unpivot the single table, copy the code from the formula bar, then delete the temporary steps to back up.  You may need to do some tweaking, of course, but at least you can easily get the syntax that way.

Now that we have this, we can finish extracting the data:

  • Right click the Stage4 column --> Remove Other Columns
  • Click the Expand icon at the top of the Stage4 column
  • Set the data types
  • Load it to your destination

Sample File

If you'd like to download the sample file, you can do so here.

 

Unfill in Power Query

Recently I received a question on how to Unfill in Power Query.  In other words, we want the opposite of the Fill feature, which fills data into blank cells (cells that contain a null value.)  If we see repeating values, we’d like to keep only the first, then replace all subsequent duplicate values with the null keyword.

Now I’ll be honest that I’d typically never do this.  I’d load the values into a table, then use a PivotTable to show the data the way I want to see it:

A table with repeating values, and a pivottable that suppresses repeating values

But having said this, if you need to have your data look like this…

A table of Animals, Colour and Amount that shows blanks under each repeating animal

… well then why not?

Unfill data with Power Query – Step 1

The first thing we need to do is run our recipe for numbering grouped rows.  (You can find this in our Power Query Recipe Cards, or in our Power Query Academy videos.)

Namely, it looks like this:

  • Sort the data by Animal to order it
  • Group the data by Animal
    • Add a single aggregation called “Data” for All Rows
  • Go to Add Column -> Custom Column and use the following formula
    • =Table.AddIndexColumn([Data],"Row",1,1)
  • Right click the “Custom” column -> Remove Other Columns
  • Expand all columns from the Custom Column

You’ve now got your rows numbered:

A Power Query showing Animal, Amount, Colour and a Row Number where each row with the same animal has a unique value starting from one

Unfill data with Power Query – Step 2

Once you’re in this state, it actually becomes pretty easy:

  • Go to Add Column -> Custom Column and use the following formula
    • = if [Row] = 1 then [Animal] else null
  • Remove the [Animal] column and the [Row] columns
  • Re-order the columns as desired
  • Rename [Custom] to Animal
  • Set the data types

Once done, you’ll notice that we have unfilled the data nicely.

A Power Query showing Animal, Colour and Amount, but only the first instance of a Animal is shown in the Animal column with duplicates showing as null

Final Thoughts

As I mentioned at the outset, this isn’t something I ever anticipate myself doing.  But if you do have a good business use case, I’d be curious to know what it is.  (I assume the asker did – although it came from a comment on an old blog post, so haven’t been able to ask.)  Please share in the comments. ?