Change data source to external

Anonymous
2025-07-06T07:10:07+00:00

In a workbook with one Power Query connection I want that for my pivot table how do I select the query?

Microsoft 365 and Office | Excel | For education | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments
Answer accepted by question author
Andreas Killer 143.6K Reputation points Volunteer Moderator
2025-07-06T07:56:38+00:00

You can not use a query directly as source for a Pivot table, the reason is that a query is just a code that pulls data.

A Pivot table needs "a space" that contains the data.

Therefore the are 2 ways:

a) Load the query as table into a sheet.

b) Use the Data Model:

Open the Queries and Connection pane, right-click the query and choose "Load To..."

In the next window check "Load the data into the Data Model" and click Ok.

Use the Data Model as source for the Pivot table. Unfortunately, you can not change the source of a regular Pivot table to the Data Model, you have to re-create the Pivot table.

The reason for that is that this kind of Pivot table has much more possibilities and support DAX... it's a complete new world where Power Pivot also comes into play.

Andreas.

Was this answer helpful?

1 person found this answer helpful.
0 comments No comments

1 additional answer

Sort by: Most helpful
  1. riny 21,600 Reputation points Volunteer Moderator
    2025-07-06T08:30:16+00:00

    But you can load the query to a PivotTable Report directly from the Queries & Connections panel. Right-click on the query, 'Load To...' and then 'PivotTable Report'. No need to load it to an Excel table or the DM first, although the DM option may be preferred if you require more complex analysis tools.

    Was this answer helpful?

    0 comments No comments