How to edit load options in Excel Power Query

In an earlier post I covered how to load data in Power Query. But what about the other side of the ETL pipeline — that is, loading and editing the connection to those queries? Let’s practice on a workbook that already has a simple loaded query. Can we figure out to change from a table to a PivotTable, for example?

Accessing premade queries

Unsure whether this workbook is using Power Query? We can find out by heading to the Data tab, then Queries & Connections under the Queries & Connections menu. A Queries & Connections pane should appear to the left of your data:

It looks like there is one query in this workbook called penguins. To find where this data loaded into the workbook, we can click on that query name:

In this case, the results of the penguins query have been loaded to a table. This is the default behavior of Power Query. But we actually have a few options for how to load the data. Right-click the penguins query and select Load To to change:

After this, you should see this menu which may look familiar from the first time you closed & loaded the data:

Let’s walk through these options:

Load options

First, you have four options for how to load the data into the workbook:

• A table. This is the default setting and is a table as in the Ctrl + T kind in Excel.
• A PivotTable “Report.” Don’t let the language scare you off, this is just another PivotTable.
• A PivotChart. As the name suggests, it’s a PivotTable plus a chart.
• Only Create Connection. With this one, the results of the query are not loaded to Excel, but the query is available in the background. You will still find it under Queries & Connections.

Placement options

Once you’ve decided the type of load, you choose where to place it: an existing or new worksheet. You can also choose the cell reference to start loading the data into that worksheet.

Data model

Finally, you have the option to add your query data to the Data Model. If you’ve heard of Power Pivot and DAX, that’s where this option leads you to! You basically get to build a relational data model inside your workbook:

Changing the workbook’s load type

Let’s go ahead and change the load type of our penguins query to PivotTable Report. We’ll go ahead and write it directly over where the current table is. You will get a warning that the data has already been loaded into Excel; go ahead and click OK to overwrite your changes.

This is a good time to point out that once data has been loaded into Excel from Power Query, you probably shouldn’t make too many changes to it because if at any time the query is refreshed, deleted, etc, you may lose a lot of work.

Deleting the Excel data does not delete the query

Keep in mind that when you delete a table or PivotTable, etc that was loaded from Power Query, you do not delete the underlying query! Take a look at what I mean by deleting the worksheet that contains your new, blank PivotTable:

After you delete the PivotTable, you’ll notice that penguins is listed as “Connection only” under Queries & Connections.

Not what you want? You can delete the query altogether by right-clicking under Queries & Connections and selecting Delete, or you can load it into yet another worksheet, among other options.

The many sides of Close & Load

One of the major benefits of Power Query is that it can be used to build repeatable data cleaning processes. But exactly how repeatable is it if it’s not clear how to change the way these queries get loaded into your workbook? I hope this post helped with the maintenance of your workbooks. What other tips or questions do you have about keeping Power Query workflows a well-oiled machine in your workplace? Let me know in the comments.

You can learn more about Power Query in my book Modern Data Analytics in Excel: