CODEWITHSUNDEEP
powerpivotpowerquery
machump
machump

Reputation: 1267

Excel 2016- Load power query directly into power pivot

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?

Upvotes: 4

Views: 16508

Answers (2)

Néstor Waldyd
Néstor Waldyd

Reputation: 1024

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

Upvotes: 2

Mike Honey
Mike Honey

Reputation: 15027

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.

Upvotes: 9

Related Questions