Forum Discussion

ItsmeFelix's avatar
ItsmeFelix
Frequent Visitor
2 years ago
Solved

Compare what changed in two lists

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:

IDFruit
1apple
2apple
3banana
6orange
8banana

e.g. Table2:

IDFruit
1apple
2apple
3banana
4apple
8orange

 

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)

 

  • 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...

     

2 Replies

  • 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...