Forum Discussion

MichielS340's avatar
MichielS340
Brass Contributor
Dec 01, 2024
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 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

  • Lorenzo's avatar
    Lorenzo
    Silver 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

     

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond 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))

    • MichielS340's avatar
      MichielS340
      Brass 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.

      🙏 

Resources