Forum Discussion
Post ProdigyConditional 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 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 |
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.
4 Replies
v-jingzhangCommunity Support
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.
IFHi, 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
