Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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. chang...
Need Help
  • AlB's avatar
    AlB
    5 years ago

    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 

     

SebSchoon1's avatar
SebSchoon1
Post Patron
4 years ago

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))
AlB's avatar
AlB
Community Champion
4 years ago

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.

 

  • SebSchoon1's avatar
    SebSchoon1
    Post Patron
    4 years ago

    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 ^^