Hello,

I assume you also made the ALL query as a connection only. If so then you need to right click the query and choose Load to... option and there choose Pivot Table report.

Br,
Anders

Hi Mohamed,

Thanks for the video that very clearly displays the issue. As Anders said, you should simply right-click the ALL query and change the Load To.

When you Insert PivotTable > and choose 'Use an external data source' you are creating another connection and therefore a new query, even though said query is already in the file. The correct way to use the ALL query in the current file as the source of a PivotTable is to right-click the ALL query in the queries pane > Load to and choose PivotTable from that menu.

Mynda

Thanks for replying.

Please, when I try to do the recommended action I do not see or do not find any option to load to PivotTable

I can only see the below 

What could be wrong?

Thanks

Your version of Excel doesn't have the option to load directly to a PivotTable, so you will need to choose Load to Table and then insert your PivotTable from that table.

It seems that you are right because I've 2016 on this machine while 2019 on the other one.

Thanks a lot 🙂

Note: This forum is extremely useful compared to others a like ones. 

Hi Mynda

I would like to create multiple Pivot Tables from a single Power Query connection.
So far I have come across three scenarios, all of which are tricky ..
If I use "Load To" as recommended in this thread, I create an exclusive connection for the query. No new Pivot Tables are permitted.
If I create the Pivot Table first and point to the connection, Power Query creates a duplicate of this connection.
If I copy the Pivot Table created from "Load To" it suppresses the duplicate query.

Is this intended functionality?
And if not can you recommend a more explicit way to connect multiple Pivot Tables to a single Power Query Connection?

Many thanks and best regards

Dave White
Mendip, UK

Hi Dave,

Your second option is correct; Close & Load to > PivotTable report. Build your PivotTable. Then copy it and paste in a new location and modify for the second PivotTable and so on.

Alternatively, you can Close & Load to > Connection only & Add to Data Model, then you can create Power Pivot PivotTables via the Insert > PivotTable > Use this workbook's data model.

Mynda

Thanks, so much, Mynda
I am reducing workloads on a daily basis thanks to your inspirational videos on Power Query.
Latest win: a simple Pivot Table to summarise Sage Cloud Payroll Faster Payment Summaries (FPS).
These XML RTI submissions are one of the most important parts of our Small Business Economy, and covid has seen huge challenges over here with many staff on what is called Flexible Furlough and employers not quite sure of their ongoing monthly costs.
I can now simply download clients' FPS reports to a Data Depot folder in Sharepoint and use Power Query to parse whatever files are in the folder and feed a  one-click refreshable monthly Pivot Table in Excel.
As you said in your video - Power Query is now a genuine game changer!
Best regards
Dave    

Wow, great to hear, Dave!