Forum Discussion

Mederic's avatar
Mederic
Post Patron
2 years ago
Solved

Identify the differences between 2 tables

  • Hello,

I would like to obtain the result shown in the screenshot below using the Table.RemoveMatchingRows formula:
I can't do it because there are the following steps to be carried out:
- match the column names
- multiply the Table2 column by -1
- and keep only the differences greater than or equal to -0.2 or 0.2

For information, my tables contain several thousand rows. 

Thanks in advance

Best regards


 

 

ID_1	Amount_1
23	345,34
45	726,71
14	234
54	62,58
66	245,28
58	58,56
45	100
823	762
77	62

 

 

 

ID_2	Amount_2
23	-345,34
45	-726,72
14	-234
66	-245,28
58	-38,88
45	-100
54	-62,6
77	-62
523	89

 

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    2 years ago

    HI Mederic 

     

    Download example PBIX file

     

    After importing both tables you can Append them.

     

     

    Then Group by ID and choose Sum as the aggregation.

     

     

    Then remove Amounts you don't want

     

     

    Here's the query for the merge onwards

     

     

    let
    Source = Table.Combine({Table1, Table2}),
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Sum", each List.Sum([Amount]), type nullable number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Sum] >= 0.2 or [Sum] <= -0.2)
in
    #"Filtered Rows"

     

     

    I nortice that in your result you've got the Amount 58.56 for the ID 58, rather than the difference between the 2 amounts.

     

    If you want to have this Amount from Table1, do a merge with my result table and Table1 to pull in the desired Amount, then delete unwanted columns.

     

    Regards

     

    Phil

2 Replies

  • Mederic's avatar
    Mederic
    Post Patron
    2 years ago

    Hello PhilipTreacy ,

    Thank you very much for this simple and effective solution,
    I hadn't thought of it
    Regarding your comment, the Amount column is not important, as long as we identify the ID references that are missing or out of date.
    Your solution is very good

    Have a nice day

    Best regards

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    2 years ago

    HI Mederic 

     

    Download example PBIX file

     

    After importing both tables you can Append them.

     

     

    Then Group by ID and choose Sum as the aggregation.

     

     

    Then remove Amounts you don't want

     

     

    Here's the query for the merge onwards

     

     

    let
    Source = Table.Combine({Table1, Table2}),
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Sum", each List.Sum([Amount]), type nullable number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Sum] >= 0.2 or [Sum] <= -0.2)
in
    #"Filtered Rows"

     

     

    I nortice that in your result you've got the Amount 58.56 for the ID 58, rather than the difference between the 2 amounts.

     

    If you want to have this Amount from Table1, do a merge with my result table and Table1 to pull in the desired Amount, then delete unwanted columns.

     

    Regards

     

    Phil