topic Re: Append two tables based on the conditions in DAX Commands and Tips

Append two tables based on the conditions

Learner27 — Thu, 19 Oct 2023 11:33:21 GMT

Hi Everyone.

I have two tables, and I need to append them based on a condition: where table 1 column 1 is not in table 2 column 2

Table 1

Column1 Column2

ABC 1
ABC 2
BCA 3
CZA 4

Table 2

Column1 Column2

ABC 1
BCA 2
ABC 3

Expected Output (after appending both tables):

Column1 Column2

CZA 4

Please provide me with the Power Query steps to achieve this."

Re: Append two tables based on the conditions

OwenAuger — Fri, 20 Oct 2023 10:00:48 GMT

Hi Learner27

Could you clarify the logic?
Should the condition be Table 1 Column 1 is not in Table 2 Column 1?
Also, in your example, the expected output includes only a row from Table 1. Could you give an example where the output would include rows from Table 2 (to help clarify the logic)?

Regards

Re: Append two tables based on the conditions

Anonymous — Tue, 24 Oct 2023 09:58:34 GMT

Hi Learner27 ,

I suggest you to try code as below in Power Query Editor.

let Source = Table.Combine({#"Table 1", #"Table 2"}), #"Filtered Rows" = let _Query = Table.ToList( Table.SelectColumns(Table.NestedJoin(#"Table 1", {"Column1"}, #"Table 2", {"Column1"}, "Table 2", JoinKind.Inner),"Column1"),Combiner.CombineTextByDelimiter(",")) in Table.SelectRows(Source, each not(List.Contains(_Query, [Column1]))) in #"Filtered Rows"

New Table:

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.