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.