+ Reply to Thread
Results 1 to 9 of 9

Combine multiple tables using pivot table

  1. #1
    Registered User
    Join Date
    08-02-2021
    Location
    Germany
    MS-Off Ver
    365 Enterprise
    Posts
    4

    Combine multiple tables using pivot table

    Hi everyone!

    I'd appreciate if someone could help me with the following problem:

    I have multiple flat tables (sales forecasts) from different sources, which I'm trying to combine into one pivot table. The tables are strucutred the same way (i.e. same columns) but have vastly different sizes.

    So far, I've joined them using PQ and then turned the combined table into a pivot table. I've been trying to accomplish this more elegantly without the extra PQ detour. This seems like such a basic task, but so far haven't succeeded.

    Is this possible at all, and if so, how?

    I've attached a small sample file.

    Thanks!
    Attached Files Attached Files
    Last edited by SiStr; 08-02-2021 at 12:58 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Combine multiple tables using pivot table

    I see no queries in your workbook, but I don't know why PQ would be considered a detour if you are doing it the right way. Please attach a copy of the workbook with your query or queries in it so that I can advise.

    I've attached how I'd do it - maybe you are jumping through more hoops than are necessary?
    Last edited by AliGW; 08-02-2021 at 12:03 PM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    08-02-2021
    Location
    Germany
    MS-Off Ver
    365 Enterprise
    Posts
    4

    Re: Combine multiple tables using pivot table

    Thanks for the quick reply. I didn't deem the query relevant for the sample file, seeing as I aim to solve the problem without it. I've attached a new version including the query now.

    It really is a very basic merger of the two tables, nothing more.

    The reason why I consider it a detour, is because I now have an additional worksheet, which needs to be updated every time the input data changes. Of course it's quite possible that I'm not doing it right.
    But if it's possible to set up a pivot table with multiple input sources, then it would be a simpler solution, especially when dealing with more than the two input tables.
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Combine multiple tables using pivot table

    As I suspected, you are not doing it right.

    Please see my previous post. My solution there will automatically combine all tables present in the workbook into one pivot table. If you want instructions about how to do it, just shout.

    One simple query (M Code):

    Please Login or Register  to view this content.
    Just load it to a Pivot Table Report instead of loading the table to the Excel workbook page.
    Last edited by AliGW; 08-02-2021 at 12:44 PM.

  5. #5
    Registered User
    Join Date
    08-02-2021
    Location
    Germany
    MS-Off Ver
    365 Enterprise
    Posts
    4

    Re: Combine multiple tables using pivot table

    Perfect, Thanks! Didn't see that attachment the first time. I wasn't aware you could load the query directly into a table. So that certainly is a suitable solution.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Combine multiple tables using pivot table

    Funnily enough, I only used the Load to Pivot Table option for the first time myself a while ago.

    Please note that my solution does not require you to load tables as queries - it finds them for you.

    Thanks for the rep.

  7. #7
    Registered User
    Join Date
    08-02-2021
    Location
    Germany
    MS-Off Ver
    365 Enterprise
    Posts
    4

    Re: Combine multiple tables using pivot table

    Quote Originally Posted by AliGW View Post
    Please note that my solution does not require you to load tables as queries - it finds them for you.
    I noticed. Although as I'm new to M code it took me a while to reproduce it in my actual file.
    This function seems to work quite differently from what I expected after reading the syntax. Could you explain what the repetition of the list of column names does?

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Combine multiple tables using pivot table

    I'm not too sure, to be honest - the M Code is generated automatically when you expand the content.

    I rarely tweak the M Code - it's mainly UI clicks with me.

  9. #9
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: Combine multiple tables using pivot table

    Quote Originally Posted by SiStr View Post
    .....
    It really is a very basic merger of the two tables, nothing more.
    .....

    You can merge the tables by MS Query with the below SQL statement as well, if you don't have PQ installed.

    Please Login or Register  to view this content.
    Attached Images Attached Images

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Combine 2 tables into one pivot table
    By MassiveExcelMan in forum Excel General
    Replies: 1
    Last Post: 04-11-2021, 12:33 PM
  2. Combine multiple tables into one pivot
    By KeighlR in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-07-2018, 10:36 AM
  3. Power Pivot to Combine Multiple Pivot Tables
    By chrapm1 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-07-2018, 04:20 PM
  4. Combine multiple pivots tables into one pivot chart
    By obrien918 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-13-2017, 04:29 PM
  5. How can I combine multiple tables into one pivot table?
    By crsport3 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-21-2013, 02:53 PM
  6. Replies: 1
    Last Post: 02-17-2011, 08:04 AM
  7. Replies: 1
    Last Post: 12-15-2009, 10:03 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1