DMVSleuth
Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get
Menu
The DestinationSleuth is intended to solve two main issues with regards to working with Power Query in Excel:
In this article, we’ll explore exactly what this Sleuth does, as well as how to use it.
While the Queries & Connection pane tells you the general state of a Power Query, that state is restricted to the fact that it loaded as a Connection Only, or to a destination with a certain numbers of rows loaded. That’s great, but how do you know which destination it went to?
DestinationSleuth helps solve that question, providing more information that the standard Queries & Connections pane. As you can see in the image below, DestinationSleuth lists all queries in the workbook, colour-coding them by the ultimate load destination. In addition, it also lists worksheet and even table names so that you can locate them quickly.
In the image above, we can plainly see four different colours used to highlight our queries. As per the form legend in the bottom right, the colours used represent:
Another useful feature of the DestinationSleuth is that you can filter to include or exclude certain load destinations. This can be helpful if you’d like to see, for example, only queries loaded to worksheet tables. To do so, toggle the checkboxes in the legend to hide the connection types you don’t wish to see.
Changing load destinations of queries in Excel is relatively straight forward once you know how; simply right click it in the Queries and Connections pane (not inside Power Query) and choose Load To… and you’ll be able to reconfigure it as needed.
But what if you want to change the load destination for multiple queries at once? As Excel has no ability to do this natively, you must change each load destination individually. (Right click each query in the Queries & Connections pane, go to Load To, configure the load destination and… wait.) And that is where DestinationSleuth can help.
Changing the load destinations for one or more queries involves the following steps:
In the image shown here, the user is requesting to change four queries – currently loading to a Connection Only – to load to the Data Model:
Upon clicking Change, the Queries & Connections pane will open, and you can watch as each of the connections gets loaded to the appropriate destination. Once complete, the DestinationSleuth will refresh with the updated load destinations, as shown in the image below:
When changing Load Destination options, there are a few features that you should be aware of:
Like in Windows, selecting multiple queries can be done by holding down the SHIFT or CTRL buttons as you select them. Queries can also be individually unselected by holding down CTRL and clicking on them.
DestinationSleuth will allow you to change any query type to one of four possibilities:
At this time, DestinationSleuth does not support loading tables to individually named worksheets.
If you’ve ever accidentally created a table, changed it to Connection Only, then manually deleted the worksheet it created, you’ll love this option. Not only will it remove the table when you change the load destination, it will also delete the worksheet the table was hosted on. This can be very useful if you accidentally landed multiple queries to individual worksheets in error.
It is important to realize that there are two different types of connections inside an Excel workbook; those that populate the data model, and those that don’t. The reason this is important to understand is that it can have an impact on what happens to data tables when a load destination is changed.
A point in case is when you change a query from “Load to Table” to become “Load to Table and Data Model”. While you might think that Excel would just add an additional load destination to the query, this is actually not the case. The original Table connection must be deleted and recreated as the internal connection string used to load data to Power Pivot’s Data Model is different than that of a worksheet table. Naturally, this will cause issues for any formulas or macros that are pointed at the historical table.
The following matrix describes what will happen with source tables during a change. It is completely consistent with changing load destinations manually but is something that you should be aware of.
Assume that you have connected to a table in an Excel file and referenced the table four times in the Power Query editor to create the required Fact and Dimension tables for your model. So far so good, but now you have a challenge. What you want is to load four of these table to the data model, and one to a connection as shown below, but you created all of these queries in one session inside the Power Query editor:
The problem, of course, is that you get to choose one load destination for all these tables. Which do you choose?
While it is frustrating that you can’t choose multiple load destinations inside the Power Query editor, it is even more frustrating that there is no way from the Queries & Connections pane to change the load destinations of multiple tables at once.
So how to deal with this scenario? Here’s what we do:
Why? Simple. Its all about speed. Consider a scenario where you have a complicated query chain that creates 10 tables and each has 10,000 rows of data, but where only 5 need to be loaded to the data model. Which will be faster?
It is obviously the latter, as you don’t have to wait for the data to unload when removing unneeded tables from the data model.
If you want to save yourself time in the future, do what we do: set your load destinations for all queries to load to Connection Only by default. That way, if you ever make a mistake, it happens very quickly, and you can repoint the tables to the correct destination with DestinationSleuth.
To do this, go to:
Ken showed the abilities of DestinationSleuth as part of a Monkey Tools demonstration for the KSA Excel user group. You can watch that replay on YouTube:
Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get
Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get
Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get
Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get
Article Overview One of the realities of building complex models is that we build models which take a long time to refresh. While timing with
Copyright Excelguru Consulting Inc.