• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Why inserting PivotTable using existing connection as a data source duplicates the query|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Why inserting PivotTable using existing connection as a data source duplicates the query|Power Query|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryWhy inserting PivotTable using exis…
sp_PrintTopic sp_TopicIcon
Why inserting PivotTable using existing connection as a data source duplicates the query
Avatar
Mohamed GadAllah
EGYPT - Cairo
Member
Members
Level 0
Forum Posts: 7
Member Since:
September 6, 2020
sp_UserOfflineSmall Offline
1
September 6, 2020 - 3:21 am
sp_Permalink sp_Print

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

YouTube

sp_AnswersTopicSeeAnswer See Answer
Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 873
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
2
September 7, 2020 - 8:19 am
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4519
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
3
September 7, 2020 - 9:07 am
sp_Permalink sp_Print

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

Avatar
Mohamed GadAllah
EGYPT - Cairo
Member
Members
Level 0
Forum Posts: 7
Member Since:
September 6, 2020
sp_UserOfflineSmall Offline
4
September 13, 2020 - 6:30 pm
sp_Permalink sp_Print

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 

load-to.pngImage Enlarger

What could be wrong?

Thanks

sp_PlupAttachments Attachments
  • sp_PlupImage load-to.png (15 KB)
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4519
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
5
September 13, 2020 - 6:57 pm
sp_Permalink sp_Print

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.

sp_AnswersTopicAnswer
Answers Post
Avatar
Mohamed GadAllah
EGYPT - Cairo
Member
Members
Level 0
Forum Posts: 7
Member Since:
September 6, 2020
sp_UserOfflineSmall Offline
6
September 13, 2020 - 10:59 pm
sp_Permalink sp_Print sp_EditHistory

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. 

Avatar
Dave White
Member
Members
Level 0
Forum Posts: 10
Member Since:
December 6, 2020
sp_UserOfflineSmall Offline
7
December 6, 2020 - 10:44 pm
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4519
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
December 7, 2020 - 7:53 am
sp_Permalink sp_Print

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

Avatar
Dave White
Member
Members
Level 0
Forum Posts: 10
Member Since:
December 6, 2020
sp_UserOfflineSmall Offline
9
December 7, 2020 - 10:46 pm
sp_Permalink sp_Print

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    

Smile

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4519
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
10
December 8, 2020 - 12:11 pm
sp_Permalink sp_Print

Wow, great to hear, Dave!

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Mohamed Jelle Hussein, Stanislas Louzier
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Aminu Sule
Samuel Ramirez
Abdifatah Osman
mohad borhom
Abayomi Adedeji
Ganesh MVS
Gilbert Lemek
Ashleigh Farquharson
Jayz Luu
Fred Smith
Forum Stats:
Groups: 3
Forums: 24
Topics: 6364
Posts: 27829

 

Member Stats:
Guest Posters: 49
Members: 32346
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.