A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
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.