Forum Discussion

IF's avatar
IF
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Conditional merge of tables

Hi, I have two tables. I want to have a combination of the values. If there is a repeated value in both tables, I want to consider the value in the table2. For example; date=03.2021 and Type=A is re...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi IF 

     

    Try below code

    let
    Source = Table.NestedJoin(Table1, {"date", "type"}, Table2, {"date", "type"}, "Table2", JoinKind.LeftAnti),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"date", "type", "Value"}),
    #"Appended Query" = Table.Combine({#"Removed Other Columns", Table2}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"date", Order.Ascending}, {"type", Order.Ascending}})
in
    #"Sorted Rows"

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

In your Join statement, why not use a right anti join to return only rows where Table2 date <> Tabke1 date  and Table2 type <>  Table1 type? Like this:


=Table.NestedJoin(Table2, {"date", "type"}, Table1, {"date", "type"}, "Merged", JoinKind.RightAnti)

 

--Nate

IF's avatar
IF
Icon for Post Prodigy rankPost Prodigy
5 years ago

Hi, thanks for the answer. Actually, I didn't understand how to do it. I copied the string that you sent but didn't work. How should I do it? Best