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

[Spangamamman]

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

 

[Ken Puls]

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.

 

[Spangamamman]

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
.

 

[Bill Szysz]

Hi Spangamamman,
See the attachment (if i understand correctly your needs)

 

[Spangamamman]

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

 

[Bill Szysz]

File with explanation
Regards

 

[Spangamamman]

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

 

[Spangamamman]

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.:hand:

Thank you all so much /Spangamamman

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