Forum Discussion
Remove all rows with same ID based on another row's input
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.
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"
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?
