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:
Select the queries you're after
Click the Rename Sheets button
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:
Select the query (or queries) you wish to change
Choose to change the load destination to a Table
Check the Name Sheets After Queries checkbox
Click the Update Load Destinations button
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.
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…
The model framework I’m using looks like this:
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:
So, if everything is fine, what’s the issue?
It’s all about this nagging little message:
Why is it here?
PivotSleuth and Measure Tables
When you launch PivotSleuth against this PivotTable, you see the following:
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:
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:
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.)
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.
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:
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:
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. 🙂
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”
Slobodan emailed me to describe a trick to protect Excel tables that he is using to drive data validation lists. The data validation lists are sourced from tables loaded via Power Query, and leverage a little hack to hide them from prying users eyes. I thought it would be cool if he shared it with everyone, so asked him to write up a little blog post on it, and here it is!
Take it away Slobodan…
Recently, my team and I had faced a problem with refreshing PQ tables that we managed to solve with a simple trick (no VBA coding), and shared it with Ken who asked me to share it with community. Thank you Ken for this opportunity! Glad to make some kind of contribution, to all of you PQ users.
We created calculation model for our sales people (Full cost calculation). Inside this Excel file, they have a lots of drop down lists from which they can choose customer, partner etc. The idea is to make these dropdown lists dynamic. In other words, whenever a new customer is created in SAP, they should be able to select this customer in Excel using a dropdown list. This is where Power Query comes to the rescue.
We have scheduled daily export of all our customers from SAP to a file on a network drive, and use this file as the data source for a local PQ table in the workbook. We then use our Power Query table “Customers” as the source for dropdown lists in calculation model.
How to make it fully automated? We have two goals here:
We want Power Query to be scheduled for automatic refresh on a daily basis
At the same time, we would like to protect Excel tables sourced via Power Query from careless users
For the first point, we have Power Update - a tool which allows you to schedule daily refresh.
Note from Ken: I haven't seen Slobodan's model, so there may be a need to use Power Update to do what he's doing. If you only need your Power Queries to update each time the Excel workbook is opened, however, you could force an update by changing the table's connection properties to force an update upon open.
Second issue, in order to protect Power Query table, we need to hide these sheets and protect the workbook. The end result is that our Customers table is hidden and cannot be unhidden and everything looks promising.
Of course, Excel protects the whole workbook structure using this method, which causes Power Update to fail. In fact, query refreshes also fail if we try to refresh data manually.
So the obvious solution doesn't work. I spent time Googling for solution to this but could not find one 🙂
I am not VBA guy, but I remembered one tip from Mynda Treacy’s dashboard course which I applied here.
Hide the worksheet and open the Visual Basic Editor (press Alt+F11)
In the Project Explorer Window (Ctrl + R if it's not showing) select the sheet which contains the Power Query table
In the Properties Window (press F4 to display this), set the Visible property to "2 - xlSheetVeryHidden"
Go to Tools --> VBAProject Properties --> Protection
Check the box next to "Lock Project for Viewing"
Set a password so only you can access it
Close the Visual Basic Editor
Our sheet containing the Customers table is hidden, and there is no possibility to unhide it. It doesn't even show up in the menu!.
At this point the only way to unhide the worksheet is to go into the Visual Basic Editor, and reset the worksheet's Visible property - but you protected the VBA project with a password so no one can get in there.
The great thing is that refreshing the Power Query tables will work, because you didn’t actually lock the workbook structure.
This solution is intended to protect data from regular excel users, who can easily mess up your workbook. Do be aware that users with VBA skills will be able to break the password, or extract the hidden sheet contents.