Append two tables based on the conditions

[Learner27]

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."

[Anonymous · Accepted solution]

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.

[OwenAuger]

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