Power Query’s default loading destination

Power Query’s default loading destination in Excel is to load a query to a Table in a new worksheet.

If we work with Power Query’s default loading destination setup when it is time to load a query, we will get tables added to new sheets, and then, if we do not want those tables in the workbook, we need to delete them along with their holding sheets.

Here are some scenarios where Power Query’s default setting to load as a table becomes very inconvenient:

The query reads data already existent in the workbook – If we have a table with data in the workbook that we load to Power Query to be referenced by other queries or to load that table to the Data Model, high chances are we do not want to load the query table into the workbook because the data already exists there.

Intermediate Queries – A Power Query project does not need to be very complex to contain queries that we build to support other queries. The tables resulting from intermediate queries are usually not needed to be loaded physically in the workbook.

The development phase of a project – Sometimes, during the creation of a query, we need to check something in the workbook, and for that, we must close the Power Query editor and load all the queries to the workbook. If our query is unfinished, we might prefer to keep it as a connection only.

Set Connection Only as Power Query’s Default Loading Destination in Excel | Solve and Excel

Creating several queries in the same work session in the Power Query editor – When we create one or several new queries during the same work session in the Power Query editor in Excel, and it is time to load them to the workbook, we go to File > Close & Load, and then we must pick between the options “Close & Load” and “Close & Load To…”

The option “Close & Load” will load all the new queries to the default destination defined in Power Query settings whereas “Close & Load To…” will give us the option to pick the loading destination we want. Our chosen option will be applied to all the new queries created in that work session. We cannot choose a different loading destination for each new query.

So, my preference is to have “Only Create Connection” set as the default loading destination so that I do not have to delete any undesired sheets created by Power Query. Then, I change the loading destination for the queries I want to have loaded as tables in the workbook and/or loaded to the Data Model.

To change a query’s loading destination, go to the Queries & Connections pane, right-click on the query, choose Load To and then select the option you what in the next dialogue window.

To set “Only Create Connection” set as the default loading destination in Power Query, do as follows:

go to the Data tab in the Excel workbook,
click the button “Get Data,” and then
select “Query Options.”

In the Query Options window:

select “Data Load” under the GLOBAL section, then
under “Default Query Load Settings” select the option “specify custom load settings” and

uncheck “Load to worksheet” and “Load to Data Model.” (Put a checkmark in this last option if you want Power Query to load your queries to the Data Model by default.)
Click OK

Bonus Tip: If you accidentally loaded queries as tables that you wanted to load as a Connection Only, simply delete the sheet that contains the query table and the loading destination will change to Connection Only for that query. Watch here how to do it.