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 ...
BI & Data Analysis
excel
Formulas and Functions
office 365
  • HansVogelaar's avatar
    HansVogelaar
    Dec 01, 2024

    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))

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 01, 2024

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

 

Resources