Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

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

Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

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