Forum Discussion
Solved
Compare two columns and values with array function
Hi, Somebody has a suggestion how to compare two tables that have keys and values to get a list of the differences in key but also when the value is not the same. In the attachment I would like to ...
Does this do what you want?
=LET(p_1, FILTER(Table1, BYROW(Table1[name], LAMBDA(x, XLOOKUP(x, Table2[name], Table2[value], "")))<>Table1[value], ""), p_2, IFERROR(FILTER(Table2, ISERROR(XMATCH(Table2[name], Table1[name]))), ""), VSTACK(p_1, p_2))
Power Query variant:
let
Source = Table.RemoveColumns(
if Table.RowCount( Table1 ) >= Table.RowCount( Table2 )
then Table.NestedJoin( Table1, {"name", "value"}, Table2, {"name", "value"}, "MERGED_TABLE", JoinKind.LeftAnti )
else Table.NestedJoin( Table2, {"name", "value"}, Table1, {"name", "value"}, "MERGED_TABLE", JoinKind.LeftAnti ),
{"MERGED_TABLE"}
)
in
Source
Elegant as well!
