Power Query delete row if value found in another column

[Syndicate_Admin]

Hi, I’m very new to power query but just can’t find a suitable solution. I need to remove a row if the same value exists in another column. I know I need a conditional statement then a filter but just don’t know how to put it all together. So if ID B exists in ID A remove the corresponding ID A row.

ID A.    ID B

1      
2

3

4         1

5         3

so here the first and third row in ID A would be removed. I would want to keep the row where the value is duplicated in ID B

thanks for any advice.

[wdx223_Daniel]

=Table.SelectRows(YourTable,each not List.Contains(YourTable[ID B],[ID A]))

[Syndicate_Admin]

Thanks - I'm still getting my head around all the functions available so good to know some new ones.

[sevenhills · Accepted solution]

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVKK1YlWMoIxjGEMEyDDEMwyBbKMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID A" = _t, #"ID B" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID A", Int64.Type}, {"ID B", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID A"}, #"Changed Type", {"ID B"}, "Changed Type", JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Changed Type"})
in
    #"Removed Columns"

Input:

Output:

Explanation:

You are joining back to the same table you are in and do the left anti.

Hope this helps!

[Syndicate_Admin]

Thanks, I appreciate you explaining the logic behind it. Really useful.