Conditional merge of tables

[IF]

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 repeated in both tables. I consider the value in the Table2. I listed the desired result in the TableResult.

Thanks in advance!

Table2

date	type	Value
03.2021	A	40
05.2021	B	21
04.2021	D	50
06.2021	C	40

Table1

date	type	Value
03.2021	A	23
03.2021	B	21
04.2021	A	33
04.2021	C	40

TableResult

date	type	Value
03.2021	A	40
03.2021	B	21
04.2021	A	33
04.2021	C	40
04.2021	D	50
05.2021	B	21
06.2021	C	40

[v-jingzhang · Accepted solution]

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]

Sorry, just copy and paste my formula right into a new blank query's formula editor bar. I don't see why you'd need the  subsequent =Source{0}[Merge]

--Nate

[Anonymous]

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]

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