Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago
Solved

Power Query delete row if value found in another column

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 jus...
  • sevenhills's avatar
    2 years ago

    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!