• 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
    • SALE 20% Off All Courses
    • 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
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Combining Multiple Tables into One Pivot Table Using an External Connection|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Combining Multiple Tables into One Pivot Table Using an External Connection|General Excel Questions & Answers|Excel Forum|My Online Training Hub
                    BLACK FRIDAY SALE 20% OFF ALL COURSES
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
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 ForumGeneral Excel Questions & Answe…Combining Multiple Tables into One …
sp_PrintTopic sp_TopicIcon
Combining Multiple Tables into One Pivot Table Using an External Connection
Avatar
PaulFogel
Member
Members
Level 0
Forum Posts: 81
Member Since:
July 7, 2016
sp_UserOfflineSmall Offline
1
October 31, 2019 - 3:47 pm
sp_Permalink sp_Print

I have a Pivot Table created with an external connection to a table in another Excel file. That other file has multiple tables, and now I want to add some of those tables to my existing Pivot Table. What are the steps to do this? Or is there a post on this on the MyOnlineTrainingHub blog?

Paul

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4647
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
October 31, 2019 - 6:12 pm
sp_Permalink sp_Print

Hi Paul,

The modern way to get data form an external workbook is with Power Query. With Power Query you can merge/append data in multiple tables and then analyse it in a PivotTable in the same workbook or an external workbook.

I don't have a tutorial on the blog on that scenario, but it is covered in my Power Query course.

Mynda

Avatar
PaulFogel
Member
Members
Level 0
Forum Posts: 81
Member Since:
July 7, 2016
sp_UserOfflineSmall Offline
3
November 1, 2019 - 8:56 am
sp_Permalink sp_Print

Hi Mynda,

Normally, that approach would work well. But in this case, I created an External Connection Only through Power Query and then created a Pivot Table using that external connection, because the source table is huge and I didn't want to load it into my destination file. But now I want to add more tables to that Pivot Table, whose total rows are well over a million if combined. I thought the answer was in the "More Tables" option in the Pivot Table field editor, but I can't get it to work the way I want.

Paul

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4647
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
November 1, 2019 - 2:04 pm
sp_Permalink sp_Print

Hi Paul,

Ok, I misunderstood because you didn't mention that you'd used Power Query to get the data initially, you just said you used an 'external connection' which I took to mean you simply connected to a Table in another file. I think there is still some information missing about how you got the data and connected to it for me to fully understand.

I didn't suggest for you to load the data into a Table in the worksheet in your destination file. You can get the data with Power Query and 'Close & Load' as a connection only and analyse in a PivotTable with the data never touching the grid. Or you can add it to the Data Model as a connection only and analyse in a Power Pivot PivotTable. Either way the data doesn't go to the Excel grid. 

What I don't understand is what you mean by 'add more tables to that PivotTable'. Do you mean 'add more data to the PivotTable source data from other tables'? If so, the method is still to use Power Query to consolidate (merge/append) the tables into one. You either do that in the original query file, or you create a new query that consolidates all of the tables and then 'close & load to' 'connection only' and 'PivotTable' or 'add to data model' and create a Power Pivot PivotTable.

I hope that makes sense.

Mynda

Avatar
PaulFogel
Member
Members
Level 0
Forum Posts: 81
Member Since:
July 7, 2016
sp_UserOfflineSmall Offline
5
November 1, 2019 - 3:16 pm
sp_Permalink sp_Print

Mynda, I was wrong--the reply you sent provided the key. I had four tables to append. I created a Connection Only for each of them, and then chose Combine Queries from the Get Data tab. When that's appended (in my case) I created yet another Connection Only from it. Now the newly created append query had all four tables' worth; I then created a new Pivot Table using this append query as the external connection source.

It's a different way of thinking to what I'm accustomed. This will be valuable as a skill to handle large data sets--break them into separate tables, then merge or append them as needed.

Thanks!

Paul

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Jorn Bast, Tracy Lippiatt, Jane X, Xuan Wang
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
Catalin Bombea: 1864
SunnyKow: 1432
Anders Sehlstedt: 886
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 241
Hans Hallebeek: 222
Jessica Stewart: 219
A.Maurizio: 213
Newest Members:
Xuan Wang
Karen Childress
Ben Penney
Karen Quagliano
Natsuko Kojima
George Girhiny
Jack Ennis
Andrew Griffin
Mostafa Younes
Alan Lim
Forum Stats:
Groups: 3
Forums: 24
Topics: 6637
Posts: 29114

 

Member Stats:
Guest Posters: 49
Members: 33046
Moderators: 2
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Jessica
Moderators: Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • 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

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

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

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.