Excel 2016- Load power query directly into power pivot

3
votes

Is there a way to load power query data directly into power pivot without creating an excel table as an intermediary step?

All the examples I've found reference Excel 2010 and 2013. Although the instructions are similar, it does not work in 2016.

In following the steps found. when I go to "Existing Connections" in Power Pivot and try to open the Power Query connection. I get a message:

"The connection you’re trying to open was created with Power Query. To change this connection, use Power Query."

Is it possible to clean/transform data using power query and load it directly to power pivot in excel 2016?

2
powerpivotpowerquery
The message you get doesn't mean that an Excel table was created as an intermediate step. It just tells you that the connection was created with Power Query (and loaded directly into your data model). – MarcelBeug
but I still cannot load the data into Powerpivot – machump

2 Answers

8
votes

I would go to the Data Ribbon and choose Show Queries. Depending on your Office/Excel update schedule, this may be changed to Queries & Connections. Either way, you are trying to open the Workbook Queries pane (appears on the right).

Within the Workbook Queries pane, right-click each Query and choose Load To. Ensure the first option is set to Only Create Connection, and that the Add this data to the Data Model option is checked.

With those options set, Load performance should be a lot faster, and you can exceed Excel's million row per table limit.

1
votes

On the Excel Workbook Ribbon:

Option 1:

Go to: Power Pivot ↦ Add To Data Model

enter image description here

Option 2:

  1. Go to Data ↦ Queries & Connections

enter image description here

  1. right-click over the query you want to add to PowerPivot ↦ Edit

enter image description here

  1. On the Power Query Editor: File ↦ Options & Settings ↦ Query Options ↦ Check Load to Data Model.

enter image description here

  1. Finally: File ↦ Close & Load