Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Remove all rows with same ID based on another row's input

Hi, I have a table like the one below but with more columns. If I wanted to remove someone who has Status = Complete, but remove all of their data how would I do so? I have tried this but it is only...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Here's my solution.

    1.Group by ID.

     

    2.Add a custom column to check if Status = "Complete".

     

     

    3.Remove the rows with ture.

     

     

    4.Expand the Count column and remove the unneeded column.

     

    You can check more details from my attachment.

     

     

     

    Best Regards,

    Stephen Tao

     

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

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

Since performance is not a major consideration here, you can perform Left Anti join after filtering on Complete. Replace Source appropriately here. 

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Status", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Status] = "Complete")),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Filtered Rows", {"ID"}, "Filtered Rows", JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Filtered Rows"})
in
    #"Removed Columns"

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Where would I paste this?

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Most Valuable Professional
      4 years ago

      Import your data into Power Query.

      Home tab - Advanced Editor

      Copy the line beginning with Source from there into notepad.

      Now remove everything from Advanced Editor.

      Paste my code there.

      Delete the source line from my code.

      Paste the source line from notepad there as second statement i.e. after let i.e. in place where my original source line was there. Ensure that comma is at the end. 

      Press OK