NOTE: This post was created using the November 2014 version of Power Query. In later versions, some of the screens or functionality could be different.

You can download the Excel 2013 file necessary to perform the steps in this post here. To get to the existing query, click the Workbook Queries button on the Power Query ribbon.

Once you have constructed your query, it is time to Close & Load it. This is the process of saving the query for use and also, optionally, executing it at the same time.

You have a total of 4 options for loading your query results:

• Load it to the Excel Data Model (Power Pivot) in the same Excel workbook housing the query (This is the option I use most frequently and recommend highly)
• Load it to a worksheet in the same Excel workbook housing the query (the Default)
• Load it to the Excel Data Model AND a worksheet
• Load it to neither of the above options (load it nowhere at all, just creating the query)

Note: Up to this point, I have kept the default name for the query we have used, Query1. I have closed and loaded it several times with this name. However, I advise you, with your own queries, to give them appropriate names before you Close & Load them. I just wanted to keep the focus of each post narrow and didn’t want to deal with it until now.

Load to Data Model

We will start this post by opening our existing query and giving it a proper name.

1. Double-click the query Query1 you see in the Workbook Queries pane of the Power Query ribbon, as shown in Figure 1.

Figure 1

This will open the Query Editor shown in Figure 2.

Figure 2

The name of the query is found in the PROPERTIES section of the Query Settings pane, just above the APPLIED STEPS. The query name will determine the name of the table when the data is loaded. For example, so far, I have been loading my query to the Data Model. So, if I open my Power Pivot management window in this file, we can see there is a single table there called Query1, as shown in Figure 3.

Note: I am not expecting you to take this particular step. I show this merely for illustration.

Figure 3

This is the final destination for the data that runs through the Query1 query.

2. Give your query an appropriate name in the PROPERTIES, such as Laureate. An example is shown in Figure 4.

Figure 4

3. Click the Close & Load button, shown by the arrow in Figure 4. This will execute the query and load its results once again to the Excel Data Model. This does not duplicate what was already there for this query. Rather, it discards the data that was there and populates it again. The results are shown in Figure 5.

Figure 5

Grand. The renaming of the query carried to the Workbook Queries, as you would expect. However, it did NOT carry through to the Data Model. Figure 6 shows my data model again, this time after the renaming of the query.

Figure 6

Hm. The name of the table is still Query1, the original name. At first, I was disappointed that the renaming did not carry into the data model. Then I realized it was a very good thing that the original table name remained. Imagine I had created lots of DAX Calculated Columns and Calculated Measures referencing this table. If the table then got renamed, those calculated columns and fields would be in danger of breaking since the table name they were written to use would no longer exist. That could break my entire data model. So, the fact that the table in the Data Model retained its original name is a very important safety feature.

This also speaks, once again, to my recommendation for naming your queries before you load them anywhere. If I had named my query Laureate before that first Close & Load way back in the first post, then my table here in the Data Model would be named Laureate and all would be well. As it stands, since we will not be doing anything with this Data Model, I will not worry about it.

Figure 7 shows a different view of the of the Excel file, allowing you to see the entire file.

Figure 7

Notice there is only a single worksheet, Sheet1, and that it has no data. This is because I have thus far been loading to the Data Model alone.

Load To Worksheet

We can change the loading destination for an existing query. There are a few options for how to accomplish this. For now, we will use the Right-Click method.

4. Right-Click the Laureate query. This will reveal the menu shown in Figure 8.

Figure 8

5. Choose the Load To option shown by the arrow in Figure 8. This will reveal the Load Options dialog shown in Figure 9.

Figure 9

6. Click the checkbox next to Load to Worksheet to select it.

7. Click the checkbox next to Load to Data Model to deselect it. The Load Options dialog should now resemble Figure 10.

Figure 10

8. Click OK. This will reveal the warning shown in Figure 11.

Figure 11

Power Query knows that we have previously loaded the data resulting from the query to the Data Model. Since we have just deselected the Data Model as an option in Step 7, Power Query is letting us know that the table in the Data Model corresponding to this query will be deleted. Yikes. This is a good safety measure. In our case, losing that table is fine.

9. Click Continue. This will execute the query and load it to a table in a new worksheet, as shown in Figure 12.

Figure 12

Load To Data Model AND a Worksheet

I first want to say that I have yet to come across a situation where it made sense to me to load data to the Data Model AND a Worksheet from a single query. However, it is an option, so I will show you how.

I mentioned earlier that there was more than one way to change where a query loads. Above, I showed the Right-Click option. There is another way to do it that does not require a right-click and is great for devices that do not support right-clicking.

10. Hover the mouse cursor of the Laureate query in the Workbook Queries pane. This will activate a little preview pane for the query, called the Peek, as shown in Figure 13.

Figure 13

11. Click the ellipsis shown by the arrow in Figure 13. This will reveal the menu shown in Figure 14.

Figure 14

12. Click the Load To option shown by the arrow in Figure 14. This will activate the Load Options dialog shown in Figure 15.

Figure 15

13. Click the checkbox for Load to Data Model. The Load Options box should now resemble
Figure 16.

Figure 16

14. Click Load. This will reveal the warning shown in Figure 17.

Figure 17

This warning is a bit different than the one we encountered above in Figure 11. It tells us that changing the load options to add the Data Model as a target will cause Power Query to rebuild the table in the Worksheet, thereby breaking references to that table in expressions, etc. This is not a concern in this example.

15. Click Continue. This will produce the result shown in Figure 18.

Figure 18

This is not a step you need to take here, but I want to show that there is now a Laureate table in the Data Model. This is shown in Figure 19.

Figure 19

Loading Nowhere

Loading to neither the Data Model nor to a Worksheet can be a fantastic option in some circumstances. It can be great when you want to turn a single large query into separate tables in a Data Model. This is also a fantastic option when you want to bring one column from a source into an existing query and have that data reside in a single table in your Data Model. There are other situations as well.

16. Right-click the Laureate query in the Workbook Queries pane to show the menu in Figure 20.

Figure 20

This will activate the Load Options dialog shown in Figure 21.

Figure 21

17. Click the checkboxes next to both options to deselect both. The Load Options dialog should resemble Figure 22.

Figure 22

18. Click Load. This will reveal the warning shown in Figure 23.

Figure 23

Once again, the text of the warning is contextually appropriate to the action being taken. That is very helpful. Not all Microsoft products have error messages that are this clear.

19. Click Continue. This will produce the result shown in Figure 24.

Figure 24

You will note that this operation was quicker than the others. The reason is that the query did not execute as there was no place to put the results. Thus, the query indicates “Load is disabled.” All of the steps in the query we created throughout the series are there. They just have no destination at the moment.

Changing Close & Load Default Action

By default when installing Power Query, at least as of the November 2014 release, the default behavior for the Close & Load button is to load to a Worksheet alone. The poor Data Model gets nothing. However, as a general best practice, I highly recommend loading to the Data Model and not a Worksheet. Reasons for this include the far better data compression you get when using the Data Model. Also, even in Excel 2013, the row limit for a Workbook is just over 1 million rows. The Data Model has no such limitation. There are factors that limit the amount of data overall, but no hard limit on rows. Add to that the fact that loading to the Data Model allows you to using DAX with your data and the choice is clear. As such, I find it convenient to change the default behavior of the Close & Load button to suit this preference.

The Power Query ribbon has a section called Machine Settings. This is where you can make this change.

Figure 25

20. Click the Options button, shown by the arrow in Figure 25. This will open the Options dialog, shown in Figure 26.

Figure 26

Under the Default Query Load Settings, the default setting is to “Use standard Power Query load settings.”

21. Click the radio button to the left of “Specify custom default load settings.” This will enable the options just beneath it, as shown in Figure 27.

Figure 27

22. Click the checkbox next to “Load to worksheet” to deselect it.

23. Click the checkbox next to “Load to Data Model” to select it. The Default Query Load Settings should now resemble Figure 28.

Figure 28

24. Click OK. There is not an obvious change to show so I won’t bother with an image.

This is a perfect place to stop for this post. In the next and final post in the series, we will demonstrate various ways of managing queries.