When you write a Power Query workbook in Excel 2013, you are given 4 choices of what to do with results. One of the choices is to load the resulting query directly to the Power Pivot data model. This choice does not exist in Excel 2010. It is reasonable therefore to think that you must first send the results to an Excel table and then load the table to Power Pivot, but that is actually not the case – there is a way to do it, read on.
First let’s look at the Excel 2013 UI
You have 4 choices of what to do with your Power Pivot Query in Excel 2013:
- load the results to Power Pivot
- to a table in Excel
- both 1 and 2
- neither 1 or 2
To see the choices, you need to select
- Close and Load
- Close and Load To
You will then see the “Load To” dialog as shown below. The way this dialog is laid out is a bit confusing. At the top of the dialog are 2 radio buttons. You select the first option if you want an Excel table and the second option if you don’t. Then down the bottom is a check box. You “check” the box if you want to load the data to Power Pivot and “uncheck” it if you don’t.
I have no idea why there are 2 different approaches to making these choices, but that’s the way it works. So simply select the combination that you want in order to have 1 of the 4 choices mentioned above.
How to Load to Power Pivot in Excel 2010
When you click “Close and Load To” in Excel 2010, the dialog box is a bit different. Note that there is no option to send directly to Power Pivot like there was in the Excel 2013 example above.
The best way to add the data from Power Query is to select “Only Create Connection” (shown as #1 above), then click “Load” (shown as #2 above).
Then go to the Power Pivot window and
- Click on the design menu
- Select Existing Connections
- From the list of workbook connections, select the query you just created
- Click on Open
This will open the table import wizard. Click Next.
In the next window, click Finish
And there you have it. Your Power Query results are sent directly into Power Pivot without the need to create an interim table first in Excel.
If you want a comprehensive lesson on how to use Power Query, checkout my training course here https://exceleratorbi.com.au/power-query-excel-power-bi-online-training/