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)
Or we can find it by clicking File -> Close & Load To… (see the screenshot below)
The Import Data window will open.
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.
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.
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.
From the list of queries, right-click on the query and select 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.
Power Query Series Contents
- Import Data
- Data Refresh
- Edit Queries
- Close & Load Options
- Using Parameters
- Basic Transformations
- Combine / Append Queries
- Import All Files in a Folder
- List All the Files in a Folder and File Attributes
- Import Data from the Current Workbook
- Import Data from the Web
- Unpivot Data
- Unstacking Data in a Column
- Lookup Values using Merge
- Change the Source Data Location
- If Statements for Conditional Logic
- Grouping and Summarizing Data
- Custom Functions
- Common Errors & How to Fix Them
- Tips and Tricks
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:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- 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.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: