Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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,Capture1.PNG

 

Table 1    Table 2    Output    
IDNameProduct Amount IDNameProductAmount IDNameProductAmountChange Description
1AlexA10 1AlexA10 7SteveB19Added in table 1
2AdamA10 2AdamA10 4RohanC15Added in table 2
3PaineB22 3PaineB22 6BradC16Change in table 2 compared to table 1
5RachitD18 4RohanC15      
6BradC17 5RachitD18      
7SteveB19 6BradC16      

 

 

1 ACCEPTED SOLUTION
AlB
Super User
Super User

@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 

 

SU18_powerbi_badge

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

@AlB Thanks So much.

AlB
Super User
Super User

@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 

 

SU18_powerbi_badge

scott_od
Frequent Visitor

@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_

 

SU18_powerbi_badge

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?

AlB
Super User
Super User

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 

 

SU18_powerbi_badge

Anonymous
Not applicable

@AIB- Data added in tabular form as you suggested.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors