Forum Discussion
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:
| 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)
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
- spinfuzerSolution Sage
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...
- ItsmeFelixFrequent Visitor
This worked, thank you!