Monkey Tools

Destination Sleuth

Article Overview

The DestinationSleuth is intended to solve two main issues with regards to working with Power Query in Excel:

  1. Help identify where a query has been loaded to
  2. Change multiple query load destinations at once

In this article, we’ll explore exactly what this Sleuth does, as well as how to use it.

Working with the DestinationSleuth

Identifying Query Load Destinations

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:

  • Green: Query is loaded to the Data model (only)
  • Blue: Query is loaded to the worksheet (only)
  • Orange: Query is loaded to the Data model and a worksheet
  • Yellow: Query is a connection only

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 via DestinationSleuth

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:

  1. Select what type of Load Destination you want your queries to embody
  2. Select the queries you want to change
  3. Click Change

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:

Load Destination Options

When changing Load Destination options, there are a few features that you should be aware of:

Selecting Multiple Queries

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.

Available Load Destinations

DestinationSleuth will allow you to change any query type to one of four possibilities:

  • Connection only (removing all data model or worksheet connections)
  • Data model (removing any table connections
  • Table on a New Worksheet (removing any data model connections)
  • Table and Data Model (adding the required connections that are missing)

At this time, DestinationSleuth does not support loading tables to individually named worksheets.

Delete Host 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.

Understanding What Happens to Connections Upon Change

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.

Practical Use Case Involving DestinationSleuth

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?

  • Load all five queries to the data model, then change the destination of the Excel workbook query to remove it from the Data Model?
  • Load all five queries as Connection Only, then go back and add each of the four required tables to the data model individually?

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:

  • Load them all to connection only, and
  • Launch DestinationSleuth to change the destination of the four queries required in the data model.

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?

  • Wait for all 10 to load to the data model, change 5 of them (individually) to connection only, waiting for each of them to unload before moving to the next query, or
  • Load all 10 to connection only, then use DestinationSleuth to change the 5 you need to load to the data model?

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. 

A quick tip to save you time in future...

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:

  • Data tab (or Monkey Tools) -> Get Data -> Query Options -> Data Load
  • Choose “Specify custom default load settings”
  • Make sure both Load to Worksheet and Load to Data Model checkboxes are unchecked
  • Click OK

See the DestinationSleuth in Action

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:



Related Posts


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