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

[Anonymous]

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 removing that specific row, but I need it to remove all the rows including that ID.

Thanks in advance.

ID	Status
101	Started
101	Progress
101	Complete
204	Started
204	Progress
305	Started

[Anonymous · Accepted solution]

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.

[aljrab87]

What if you have more thank just 1 criteria of Status = Completed? How would you add Status = Completed and Rank = 0 as an example?

[Anonymous]

Thank you but as I have more columns it is not working. How would I add in what you did to this as when I tried replacing it, it did not work. This is my current advanced editor:

let

    Source = Sql.Database("company"),

    dbo_People = Source{[Schema="dbo",Item="People"]}[Data],

    #"Removed Other Columns" = Table.SelectColumns(dbo_People,{"Items", "Text", "Roles", "State"}),

    #"Expanded Items" = Table.ExpandRecordColumn(#"Removed Other Columns", "Items", {"Id", "First", "Second"}, {"Items.Id", "Items.First", "Items.Second"}),

    #"Expanded Text" = Table.ExpandTableColumn(#"Expanded Items", "Text", {"Section", "Text", "Deleted"}, {"Text.Section", "Text.Text", "Text.Deleted"}),

    #"Filtered Rows" = Table.SelectRows(#"Expanded Text", each ([Text.Section] = 120) and ([Text.Deleted] = false)),

    #"Expanded Roles" = Table.ExpandRecordColumn(#"Filtered Rows", "Roles", {"End", "Projects", "Start", "Title"}, {"Roles.End", "Roles.Projects", "Roles.Start", "Roles.Title"}),

    #"Expanded Roles.Projects" = Table.ExpandRecordColumn(#"Expanded Roles", "Roles.Projects", {"Name"}, {"Roles.Projects.Name"})

in

    #"Expanded Roles.Projects"

[Vijay_A_Verma]

Table which you had posted in the problem is generated at which step? Can you post a screenshot of your final table from where you want my code to be working? 

[Vijay_A_Verma]

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]

Where would I paste this?

[Vijay_A_Verma]

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