Remove several rows with same id if one value fulfills condition

[Anonymous]

Hello all,

I am rather new to Power Bi and i have a question someone might be able to answer.

ID        Previous State    State                    Changed Date

1	Created	Work	15.07.2019
2	Work	Implemented	17.07.2019
2	Implemented	Done	17.07.2019
3	Work	Done	07.08.2019
4	Work	Implemented	26.06.2019
4	Implemented	Done	26.06.2019
5	Created	Work	02.08.2019

I have a set of data. I want to remove all IDs with the status "Done" (marked as red). This is not really a problem. 

Also, if an ID is set to "Done" I want to make sure that all rows with the respective ID are removed (marked as bold).

Something like: If [State] = "Done" then remove all rows which have the same ID as the one with "Done"

The output would be: 

ID      Previous State    State    Changed Date

1	Implemented	Work	15.07.2019
5	Created	Work	02.08.2019

Thanks a lot in advance! 

[Mariusz · Accepted solution]

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.

[Anonymous]

Thanky you very much @Mariusz !

Seems to be working perfectly but I would like to use another Query called "Merge1" I've created before as the source for this code. 

Any clues on how to add this? 

[Mariusz]

Hi @Anonymous 

Sure, as you can see on the below screenshot I've added 5 steps, this are the ones that you will need to replicate in your Merge1 query, all  you need to do is double click on my step to see the logic as on the example.

Let me know if you need any extra guidance.

Best Regards,
Mariusz

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

Please feel free to connect with me.

[Anonymous]

Thank you, it works perfectly!

[Mariusz]

Hi @Anonymous 

Great, happy to help!

Best Regards,
Mariusz

Please feel free to connect with me.