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

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”