Remove several rows with same id if one value fulfills condition

Hi Anonymous 

Please see the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIuSk0sSU0BssLzi7KBlKGpnoG5npGBoaVSrE60khFCxjO3ICc1NzUPotzQHEMdqgKX/LxUTHXGCPOgCoCyBhYIBSa4LDQy0zMwQ1WH1UI0dabYPGlghLAzFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Previous State" = _t, State = _t, #"Changed Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Previous State", type text}, {"State", type text}, {"Changed Date", type date}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [State] = "Done" then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Conditional Column", {"ID"}, {{"Rows", each _, type table [ID=number, Previous State=text, State=text, Changed Date=date, Custom=number]}, {"Count", each List.Sum([Custom]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] = 0)),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Filtered Rows", "Rows", {"Previous State", "State", "Changed Date"}, {"Previous State", "State", "Changed Date"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Rows",{"ID", "Previous State", "State", "Changed Date"})
in
    #"Removed Other Columns"

If you have not done this before, than all you need to do is create a Blank Query in New source and paste the above script into advance editor of a newly created query, from there you should be able to see all the query steps, investigate and replicate for your data set.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.