cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
basroozen Member
Member

Merge two tables if the key does NOT match

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Merge two tables if the key does NOT match

HI @basroozen,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
2 REPLIES 2
Community Support Team
Community Support Team

Re: Merge two tables if the key does NOT match

HI @basroozen,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
basroozen Member
Member

Re: Merge two tables if the key does NOT match

Hi @v-shex-msft,

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