Compare what changed in two lists

[ItsmeFelix]

Hi, 

I have two tables with IDs and another entry (lets say fruist) which I want to compare

(My real tables have thousands of rows with 7 different fruits)

e.g. Table1:

ID	Fruit
1	apple
2	apple
3	banana
6	orange
8	banana

e.g. Table2:

ID	Fruit
1	apple
2	apple
3	banana
4	apple
8	orange

I want to track if IDs got added (like ID = 4), removed  (like ID = 6) or changed  (like ID = 8 )

At the moment I have a solution that compared the IDs and can tell me if an ID got added or removed but not changed:

----------------------------------------------------------------------------------------------------------

let
removed = List.Difference(Table1[ID], Table2[ID]),
removed_com= Table.AddColumn(Table.FromList(removed),"Change", each "removed"),
added = List.Difference(Table2[ID], Table1[ID]),
added_com = Table.AddColumn(Table.FromList(added),"Change", each "added"),

my_changes = Table.Combine({added_com,removed_com})
in
my_changes

----------------------------------------------------------------------------------------------------------

I would like to see the changes too, is this possible and how?

(It would also be nice if it is possible to get the fruit for the changing IDs)

[ItsmeFelix]

This worked, thank you!

[spinfuzer · Accepted solution]

Just do a Full Outer Join and then if then statements comparing your left table to your right table values and where it is null and not null.

if [ID] = [Table2.ID] and [Fruit] = [Table2.Fruit] then...

else if [ID] = null then ...

etc...