Log In
Register
Home
September 6, 2020
Offline
Hi,
Creating a new workbook and save it with name BOOK1.
Creating 1st table for my data in BOOK1, and give it a name FIRST.
Creating 2nd table of my data in BOOK1, and give it a name SECOND.
Creating a new workbook and save it with name BOOK2.
Inserted the 1st table into the BOOK2 as a connection.
Inserted the 2nd table into the BOOK2 as a connection.
Appended both connections into a new one titled ALL.
Now when I insert a new PivotTable into BOOK2 and use ALL query as a data source, I've found a new connection being generated instead of using existing one.
I've made a video with full details
See Answer
VIP
Trusted Members
December 7, 2016
Offline
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
July 16, 2010
Offline
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
September 6, 2020
Offline
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
July 16, 2010
Offline
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.
Answers Post
September 6, 2020
Offline
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.
December 6, 2020
Offline
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
July 16, 2010
Offline
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
December 6, 2020
Offline
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
1 Guest(s)
