This post may contain affiliate links. Please read my disclosure for more info.

Power Query – Close & Load Options

Power Query - Close & Load Options

In this part of the Power Query series, we will be taking a quick look at the Close & Load options.  So far, we have only used the default option which loads the output of the query into a new worksheet.  But there are plenty of other options we could use instead.

We won’t get to use all the Close & Load settings in this series.  But you should be aware that they are there so you can make use of them when you need to.

Close & Load To… settings

Having imported the data into Power Query and made our transformations, we must then decide what to do with that query.  Clicking Close & Load will push it straight into Excel as a new worksheet.  But to see all the other options select Close & Load To…..  We can find this option in the Close & Load drop down (see the screenshot below)

Home - Close and Load To

Or we can find it by clicking File -> Close & Load To… (see the screenshot below)

File - Close and Load To

The Import Data window will open.

Import Data WindowImport Data Window

This window provides three different choices (1) How to view the data (2) Where to put the data (3) Whether to load the data into the Data Model.  Let’s look at each of these in turn.

How to view the query output

The top four options in the Import Data window relate to viewing the output:

Table – Load the data into Excel as a Table, which appears on the face of the worksheet.

PivotTable Report – The columns of the transformed data are available to use as dimensions within a PivotTable.

PivotChart – The columns of the transformed data are available to use within a PivotChart.

Only Create Connection – The output is not visible on any worksheet in any form.  However, it can be used within another query or a parameter (more on that in a future post).

Where to put the query output

The second group of options determine where the query output will be placed.  This is only relevant if we selected Table, PivotTable or PivotChart in the first group.

The options are either:

  • New worksheet (the output will start at Cell A1)
  • Existing Worksheet (on which we can define the exact cell to place Table, PivotTable or PivotChart).

Add this data to the Data Model

The Data Model is a modern way for Excel to handle data.  If we are using Power Pivot or creating relationships, then a Data Model is essential.

What if we only want a single Table to create a PivotTable?

The Pivot Cache is Excel’s way of storing data used within a PivotTable.  We cannot see the cache; it is stored invisibly within the Excel file format.  But, we can certainly see the impact of the Pivot Cache, as file sizes for workbooks containing PivotTables can become very big.

By loading the data into a Data Model, rather than a PivotTable, the file sizes are significantly smaller.

As an illustration, I loaded 1 million rows of data (containing only two columns) into the Data Model.  I then used the Data Model to create a basic PivotTable; the file size was 794KB.  By contrast, I loaded the data directly into a PivotTable without a Data Model and the file size was 3,488KB.  Over 4 times the size!  While in this illustration, the file sizes are small, but when working with more data, the variance is even more pronounced.

Therefore, even if not wanting to use Power Pivot, or create relationships, the Data Model is still a better option.

Properties button

Clicking the Properties button will open the Query Properties window, which we have already seen in the Data Refresh post.

Change Close & Load setting of an existing query

If we go back into an existing query, the Close and Load To… option will be greyed out.

Close & Load To Grey

Instead of changing the Close & Load options within Power Query, we can view them in the Queries and Connection window. In Excel click Data -> Queries & Connections.

Data Queries and Connection

From the list of queries, right-click on the query and select Load To…..

Queries Connection - Load To

Discard & Close

If we have made some changes in the Power Query editor which we no longer want, we can discard any changes by clicking File -> Discard & Close.

Discard and close

Series Contents

  1. Introduction
  2. Import Data
  3. Data Refresh
  4. Edit Queries
  5. Close & Load Options
  6. Using Parameters
  7. Basic Transformations
  8. Combine / Append Queries
  9. Import All Files in a Folder
  10. List All the Files in a Folder and File Attributes
  11. Import Data from the Current Workbook
  12. Import Data from the Web
  13. Unpivot Data
  14. Unstacking Data in a Column
  15. Lookup Values using Merge
  16. Change the Source Data Location
  17. Formulas
  18. If Statements for Conditional Logic

Leave a Reply

Your email address will not be published. Required fields are marked *