Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
@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
@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
@AlB Would it be possible to expand the code to indicate which column has been changed?
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 @AlB Great thanks!
I'll try this as soon as possible.
Waiting for this, have created a list with Intersect to find Items in Common between the two lists.
Then took full table from left, used left join on table to be compared ( selected items from list )
Then substract Ordered quantity and Confirmed quantity,
took the column in result renamed to "Quantities"
then merged.
It works, but not really nice code ^^
@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 @bi_user08
Your code is just insane !!
Is there any Way to substract value from Table 1 Column quantity to Table 2 Quantity?
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
@AIB- Data added in tabular form as you suggested.