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

  • sevenhills's avatar
    sevenhills
    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!

4 Replies

  • sevenhills's avatar
    sevenhills
    Super User
    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!

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      2 years ago

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

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago

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

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      2 years ago

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