Results 1 to 8 of 8

Thread: Power Query – combine two tables and show A L L unique keys

  1. #1
    Seeker Spangamamman's Avatar
    Join Date
    Sep 2017
    Location
    Sweden
    Posts
    19
    Articles
    0
    Excel Version
    Microsoft Office 365 Business

    Power Query – combine two tables and show A L L unique keys



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello community!

    Long time since I got in touch, but here I am again. Thank you again for getting me started with Power Query earlier. This question may seem basic but I can´t make it work. The function is applicable in many situations, but I give the present case to solve.

    I have accounting data from year 2017 and 2018 and need to combine 12 columns of 2017 and 12 columns of 2018, matching the different accounts on the same rows. (We are “cleaning up” in the accounting, by both excluding old, now useless accounting numbers, as well as adding new, relevant ones. Our accounting software can only present one fiscal year at a time, thus 2017 or 2018.)

    Example:

    Year 2017
    3010 sales Sweden
    3011 special sales to Adam, Sverige (this one is missing in 2018)
    3510 Invoiced costs
    5010 Rent…

    Year 2018
    3010 sales Sweden
    3510 Invoiced costs Sweden
    3520 Invoiced costs EU (this one is new 2018)
    5010 Rent…

    Therefore, I have tried to export 2017 and 2018 separately, and to combine them through the Power Query Editor. I believe it is called to “Merge questions” (unfortunately I have a Swedish editition of Office 365).

    In the drow-down, I choose “Complete, all rows from both”. But no matter how I try, I end up either with all data from 2017, showing mapping data from 2018, or the other way around. We need both 3011 and 3520 in the example above.
    How can this be solved? Great big thank you in advance! /Spangamamman

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I think what you really want to do is reshape your data so that it lists it in this format: Year, Account, Description

    Then you want to Append the data (not merge it) to create a narrow but longer table. If you really want your years in different columns (not a good practice for Pivot Table source data), you can always use Power Query to Pivot it. (Although I would do it with an Excel PivotTable.)

    The merge features go horizontally. You might be able to get there with a full outer join, but you enter the issue of many to many join results, which is not a good thing.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Seeker Spangamamman's Avatar
    Join Date
    Sep 2017
    Location
    Sweden
    Posts
    19
    Articles
    0
    Excel Version
    Microsoft Office 365 Business
    Hello and thanks for quick reply!
    Unfortunately, I don´t think I was clear. Please consider the image below. Should not this be an easy task for Power Query to solve, not having to go via pivot table? I think so, but I don´t make it...(1709 is September 2017 etc)

    In reality the number of rows/accounts are plenty. Kindly/Spangamamman

    Account 1709 1710 1711 1712 1801 1802 1803
    3010 50 50 50 50 50 50 50
    3011 10 10 10 10 0 0 0
    3510 50 50 50 50 50 50 50
    3520 0 0 0 0 10 10 10
    5010 50 50 50 50 50 50 50
    .

  4. #4
    Acolyte Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    73
    Articles
    0
    Excel Version
    Excel 365
    Hi Spangamamman,
    See the attachment (if i understand correctly your needs)
    Attached Files Attached Files

  5. #5
    Seeker Spangamamman's Avatar
    Join Date
    Sep 2017
    Location
    Sweden
    Posts
    19
    Articles
    0
    Excel Version
    Microsoft Office 365 Business
    Thank you Bill! You solved it elegantly, great.

    Is it possible to show a step by step instruction of how you solved the problem (primarily the Query result)? If so, I am truly grateful. (I guess too many excel users are supposed to solve problems, given very little time given to find out how…)

    In the editor, I can see you use Queries Table2017, Table2018 and Result, the clue. In Result, for example, the Souce is the complete set of used account numbers. Perfect, but how? If you can explain some other steps as well, that is extra bonus. For example some detail in your fourth step, merged Queries, where the second column has the namne PrevYear. What commands arrange this?

    Kindly /Spangamamman

  6. #6
    Acolyte Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    73
    Articles
    0
    Excel Version
    Excel 365
    File with explanation :-)
    Regards
    Attached Files Attached Files

  7. #7
    Seeker Spangamamman's Avatar
    Join Date
    Sep 2017
    Location
    Sweden
    Posts
    19
    Articles
    0
    Excel Version
    Microsoft Office 365 Business

    Power Query – combine two tables and show A L L unique keys

    Thank you again,
    your M-code and scetches have been really helpful but I cannot manage a somewhat larger copy of the true case. I can´t connect and show all values for each account. I enclose a file with a cut out from one company with the sales accounts.

    I have identified one problem, that is your very first step where you combine the tables. Your star out code is: Combine ({Table2017[[Account]], Table2018[[Account]]},
    I only get: = Table.Combine({Table2017, Table2018})

    My initial clicking must be wrong. What do I do wrong?

    Kindly /Spangamamman
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Choice of command to combine tables.JPG 
Views:	3 
Size:	67.1 KB 
ID:	8704  
    Attached Files Attached Files

  8. #8
    Seeker Spangamamman's Avatar
    Join Date
    Sep 2017
    Location
    Sweden
    Posts
    19
    Articles
    0
    Excel Version
    Microsoft Office 365 Business
    Hi again, I am sorry.

    after sorting the tab "Nestled" in column A (Accounts) I realized that I through using Merge did get all Accounts on separate rows, so I can continue my work. My solution still is not close to how pretty yours is Bill, but I am very happy that I can continue* .

    One problem, however, I have to make a work around to combine the two separate account columns (2017 and 2018) into one single column with all account numbers. That is presently an IF-formula in a new column in the result sheet (not posted here). Is there a simple explanation how I can add this column directly into my Power Query? An answer here is a bonus, I get by anyway and will keep trying to find out how to solve this case in the "pretty way" above.

    Thank you all so much /Spangamamman

    * = Table.NestedJoin(Table2017,{"Kontonr"},Table2018,{"Kontonr"},"Table2018",JoinKind.FullOuter)

Tags for this Thread

Posting Permissions

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