Forum Discussion
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 have a list of keys and values which are different between table 1 and table 2.
Many thanks for a push in the right direction,
regards,
Michiel
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))
6 Replies
- LorenzoSilver Contributor
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
- MichielS340Brass Contributor
Elegant as well!
- SergeiBaklanDiamond Contributor
As variant with Power Query
let a = Table.AddColumn( Table.RemoveMatchingRows( Table1, Table.ToRecords(Table2) ), "Table", each "Table1"), b = Table.AddColumn( Table.RemoveMatchingRows( Table2, Table.ToRecords(Table1) ), "Table", each "Table2"), c = a & b in c
How about Python
# Sample tables as dictionaries table1 = {'key1': 'value1', 'key2': 'value2', 'key3': 'value3'} table2 = {'key1': 'value1', 'key2': 'different_value2', 'key4': 'value4'} # Function to find differences between two tables def find_differences(table1, table2): differences = [] # Check keys and values in table1 for key in table1: if key not in table2: differences.append((key, table1[key], 'Key not in table2')) elif table1[key] != table2[key]: differences.append((key, table1[key], table2[key])) # Check for keys in table2 that are not in table1 for key in table2: if key not in table1: differences.append((key, 'Key not in table1', table2[key])) return differences # Finding and printing differences differences = find_differences(table1, table2) for diff in differences: print(f"Key: {diff[0]}, Table1: {diff[1]}, Table2: {diff[2]}")
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))
- MichielS340Brass Contributor
Thank you very much, This does the trick.
The split in P_1 and P_2 is something I should come up with earlier.
🙏