Identify the differences between 2 tables

[Mederic]

• 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

[Mederic]

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 · Accepted solution]

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