Forum Discussion
compare two tables and find the difference using power query
hello,
I have two excel tables table 1 and table 2. I want to compare both the tables using power query and find the difference in three forms
1. extra in table 1
2. extra in table 2
3. change in table 2 compared to table 1
pic is for reference,
| Table 1 | Table 2 | Output | ||||||||||||
| ID | Name | Product | Amount | ID | Name | Product | Amount | ID | Name | Product | Amount | Change Description | ||
| 1 | Alex | A | 10 | 1 | Alex | A | 10 | 7 | Steve | B | 19 | Added in table 1 | ||
| 2 | Adam | A | 10 | 2 | Adam | A | 10 | 4 | Rohan | C | 15 | Added in table 2 | ||
| 3 | Paine | B | 22 | 3 | Paine | B | 22 | 6 | Brad | C | 16 | Change in table 2 compared to table 1 | ||
| 5 | Rachit | D | 18 | 4 | Rohan | C | 15 | |||||||
| 6 | Brad | C | 17 | 5 | Rachit | D | 18 | |||||||
| 7 | Steve | B | 19 | 6 | Brad | C | 16 |
Anonymous
Place the following M code in a blank query to see the steps. See it all at work in the attached file.
let addedT1_= List.Difference(Table1[ID], Table2[ID]), addedT2_= List.Difference(Table2[ID], Table1[ID]), T1_ = Table.AddColumn(Table.SelectRows(Table1, each List.Contains(addedT1_, [ID])), "Change description", each "Added in table 1", type text), T2_ = Table.AddColumn(Table.SelectRows(Table2, each List.Contains(addedT2_, [ID])), "Change description", each "Added in table 2", type text), TChanges_ = Table.AddColumn(Table.SelectRows(Table.SelectRows(Table2, each not List.Contains(addedT2_, [ID])), each _ <> Table1{[ID = [ID]]}), "Change description", each "Change in table 2 compared to table 1", type text), res_ = Table.Combine({T1_, T2_, TChanges_}) in res_Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
10 Replies
Anonymous
Place the following M code in a blank query to see the steps. See it all at work in the attached file.
let addedT1_= List.Difference(Table1[ID], Table2[ID]), addedT2_= List.Difference(Table2[ID], Table1[ID]), T1_ = Table.AddColumn(Table.SelectRows(Table1, each List.Contains(addedT1_, [ID])), "Change description", each "Added in table 1", type text), T2_ = Table.AddColumn(Table.SelectRows(Table2, each List.Contains(addedT2_, [ID])), "Change description", each "Added in table 2", type text), TChanges_ = Table.AddColumn(Table.SelectRows(Table.SelectRows(Table2, each not List.Contains(addedT2_, [ID])), each _ <> Table1{[ID = [ID]]}), "Change description", each "Change in table 2 compared to table 1", type text), res_ = Table.Combine({T1_, T2_, TChanges_}) in res_Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hello @bi_user08
Your code is just insane !!
Is there any Way to substract value from Table 1 Column quantity to Table 2 Quantity?
Anonymous
Heres the transformed code i used (yours mainly)
let
Commandépasconfirmé_= List.Difference(#"Comparaison_linesheets (Step1)"[Clef unique],#"Comparaison_Extract (step2)"[Clef unique]),
Confirmépascommandé_= List.Sort(List.Difference(#"Comparaison_Extract (step2)"[Clef unique],#"Comparaison_linesheets (Step1)"[Clef unique]),Order.Ascending),
T1_ = Table.AddColumn(Table.SelectRows(#"Comparaison_linesheets (Step1)", each List.Contains(Commandépasconfirmé_, [Clef unique])), "Type de différence", each "Quantités en plus dans commande", type text),
T2_ = Table.AddColumn(Table.SelectRows(#"Comparaison_Extract (step2)", each List.Contains(Confirmépascommandé_, [Clef unique])), "Type de différence", each "Quantités en plus dans confirmation", type text),
TChanges_ = Table.AddColumn(Table.SelectRows(Table.SelectRows(#"Comparaison_Extract (step2)", each not List.Contains(Confirmépascommandé_, [Clef unique])), each _ <>#"Comparaison_linesheets (Step1)" {[Clef unique = [Clef unique]]}), "Type de différence", each (_Comparaison_linesheets (Step1)[Quantités])-(_Comparaison_Extract (step2)[Quantités]), Int64.Type),
res_ = Table.Combine({T1_, T2_, TChanges_}),
#"Lignes triées" = Table.Sort(res_,{{"Clef unique", Order.Ascending}})
in
#"Lignes triées"Hello @AIB
May I Ask you a small Adding of information?
If items are in common in both Tables (Like ORDERED Table And CONFIRMEDORDER)
Is there anyway to get the equal values on ITEM ID Then Gete The quantity columns and substract values From ORDERED quantities With CONFIRMED Quantities?
I tried using your code and adapt it.
However It does not compare Equals items (Clef unique) so substracted quantities are Wrong
here the Code
= Table.Buffer(Table.AddColumn(Table.SelectRows(Table.SelectRows(Confirmations, each not List.Contains(#"Confirmépascommandé_", [Clef unique])), each _ <>Commandes {[Clef unique = [Clef unique]]}), "Quantités en écart", each [Quantités] - List.Buffer(List.Range(Commandes[Quantités],[Quantités]-1,1)){0}, Int64.Type))Hi SebSchoon1
I've changed the query a bit to add the difference in amount. You can tweak it further to fit your needs. See it in the attached file in Report_2
let addedT1_= List.Difference(Table1[ID], Table2[ID]), addedT2_= List.Difference(Table2[ID], Table1[ID]), T1_ = Table.AddColumn(Table.SelectRows(Table1, each List.Contains(addedT1_, [ID])), "Change description", each "Added in table 1", type text), T2_ = Table.AddColumn(Table.SelectRows(Table2, each List.Contains(addedT2_, [ID])), "Change description", each "Added in table 2", type text), TChanges_ = Table.AddColumn(Table.AddColumn(Table.SelectRows(Table.SelectRows(Table2, each not List.Contains(addedT2_, [ID])), each _ <> Table1{[ID = [ID]]}), "Change description", each "Change in table 2 compared to table 1", type text), "DiffAmount", each Table1{[ID = [ID]]}[Amount] - Table2{[ID = [ID]]}[Amount], Int64.Type ), res_ = Table.Combine({T1_, T2_, TChanges_}) in res_Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Hi Anonymous
Can you share the tables in text-tabular format, so that their contents can be copied?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
AlB Thanks So much.
