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


Get to know Power Query Close & Load options

Power Query - Close & Load Options

In this post, we are looking at the Power Query Close & Load options. This is part of a series designed to get you up and running with Power Query quickly. So far in the series, we have only loaded data into Excel via a Table. But there are lots of other options to explore.

While we won’t be using all the Close & Load settings in this series, it is important to be aware of them. Then, if and when the situation arises, you can review the options and pick the best choice for your scenario.

Close & Load settings

Having imported data into Power Query and made our transformations, we must decide what to do with that query. We cannot leave the data in Power Query until later; it needs a load destination.

If the default options are applied, clicking Home > Close & Load will push the data straight into Excel as a Table on a new worksheet.

The advantage of selecting Close & Load To… is we get to see all the options, rather than just loading to the default location. Select Home > Close & Load (drop down) > Close & Load To… (1), or an alternative is File > Close & Load To… (2).

Power Query Close & Load Menu

 

what is the advantage of selecting the “load to” option?

The Import Data window opens.

This window provides three key 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 sections in turn.

Import Data dialog box

How to view the query output

The top section in the Import Data dialog box determines what type of object the data is loaded into:

  • 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 data is not visible on any worksheet as a Table, PivotTable, or PivotChart.

It may appear that only creating a connection is a bit pointless. After all, why import data if you can’t see it anywhere. However, even though the data is not available on the face of a worksheet, it can still be available to the Data Model and other queries.

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 – where we can define the exact cell to place the Table, PivotTable or PivotChart).

If we are only creating a connection, these options are disabled.

Add to the Data Model

The Data Model is a modern way for Excel to handle data. A Data Model is essential if using Power Pivot or creating relationships.

But the Data Model isn’t just for using Power Pivot. The Pivot Cache is Excel’s usual way of storing data used with a PivotTable. We cannot see the cache; it is stored invisibly within the Excel file format. But, we can certainly see the impact; file sizes for workbooks containing Pivot Caches can become huge.

The file sizes are significantly smaller by loading the data into a Data Model rather than a Pivot Cache. Therefore, when creating a PivotTable, we simply need to select the Data Model as the source.

Create PivotTable from Data Model

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, loading the same data directly into a PivotTable using the Pivot Cache and the file size was 3,488KB. Over 4 times the size! When working with more columns or more rows, the variance is even more pronounced.

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

Properties button

Clicking the Properties at the bottom of the Import Window opens the Query Properties window, which we have covered in another post: Power Query – data refresh.

Change Close & Load settings of an existing query

So, we’ve created a query and loaded it into its destination. What happens when we want to change the destination? You would think we head back into Power Query and select a new destination in the Import Data dialog box. However, if we go back into an existing query, the Close and Load To… option is greyed out (see screenshot below).

Close & Load to greyed out

Don’t worry; there is another way.

Why is Close and Load Greyed Out?

Instead of changing the Close & Load options within Power Query, we can view them in the Queries and Connection pane. In Excel, click Data > Queries & Connections. From the list of queries, right-click on the query and select Load To…

In Queries & Connections click Load To to change the load location

This opens the Data Import dialog box again. From there, we can change the load settings.

Take care when making changes here; anything based on the existing data may be affected.

Close and load applies to all new queries

Now it’s time for a small warning.

As our abilities with Power Query increase, we may create multiple queries in a single session. Once we are happy with them, we then close and load them into Excel.

The Import Data dialog box selections are applied to all new queries. If we want different queries to be loaded differently, it is better to load them all as connection only, then go back and change them as we wish. Otherwise, we can spend a lot of time deleting Tables, PivotTables, and worksheets we never wanted.

Changing the default Close & Load settings

It is a good idea to adjust the default Close & Load settings so that it works how we work.

The default settings can be changed in the Query Options section. To reach this from Excel click Data > Get Data (drop-down) > Query Options. Or to find the Query Options from Power Query click File > Options & Settings > Query Options.

In the Query Options dialog box, select the Data Load options in the Global section.

Default Query Options for Power Query

Here we can find the Default Query Load Settings. I recommend disabling both of the following options:

  • Load to worksheet: Uncheck
  • Load to Data Model: Uncheck

With both unchecked, this makes connection only the default. As noted above, since all new queries are treated the same way, connection only is the best choice for us.

Click OK to close the Query Options dialog box.

Discard & Close

Finally, the last Close & Load option is to discard any changes entirely. To do this, in Power Query, click File > Discard & Close.

Wrap-up

This post has looked at the options for loading data from Power Query into Excel. While you may not use them all straight away, it’s good to be aware of these settings for future reference.

The best time saver you can use is to set the defaults so that Power Query loads as a connection only as default.



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:

2 thoughts on “Get to know Power Query Close & Load options

  1. Phil Danley says:

    Thank you for this article. Great explanation of Only Create Connection and loading to the Data Model. Looking forward to the VBA info as I’ve never used them in Excel before…time to learn!

Leave a Reply

Your email address will not be published.