Merge two tables if the key does NOT match

[Anonymous]

Hi there,

I have one table with historic order information with order number as a primary key. It contains detailed order information

My second table also has order information with order number as a primary key. It contains limited order information.

I want to add all rows from the second table to the first table with just one rule:

If the order number already exists in the first table, then do not return any information from the second table. Or the other way around; if the order number exists in the second table but not in the first table, then add the row from the second table with that order number to the first table.

Would anyone know how to handle this?

[v-shex-msft · Accepted solution]

HI @Anonymous,

I'd like to suggest you use except function to extract unmatched orderid between table1 and table2, then use union and filter function to union two tables.

Sample formula:

New Table =
VAR idlist =
    EXCEPT ( VALUES ( Table2[OrderID] ), VALUES ( Table1[OrderID] ) )
RETURN
    UNION ( Table1, FILTER ( Table2, [OrderID] IN idlist ) )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft,

Thanks for getting me on the right way, that actually worked! Thanks for helping out & happy holidays!