cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jama1234 Frequent Visitor
Frequent Visitor

Remove several rows with same id if one value fulfills condition

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

1CreatedWork15.07.2019
2WorkImplemented17.07.2019
2ImplementedDone17.07.2019
3WorkDone07.08.2019
4WorkImplemented26.06.2019
4ImplementedDone26.06.2019
5CreatedWork02.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

1ImplementedWork   15.07.2019
5CreatedWork02.08.2019

 

Thanks a lot in advance! Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Remove several rows with same id if one value fulfills condition

Hi @jama1234 

 

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.
Mariusz Repczynski

 

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Remove several rows with same id if one value fulfills condition

Hi @jama1234 

 

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.
Mariusz Repczynski

 

View solution in original post

jama1234 Frequent Visitor
Frequent Visitor

Re: Remove several rows with same id if one value fulfills condition

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? 

Super User
Super User

Re: Remove several rows with same id if one value fulfills condition

Hi @jama1234 

 

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.

image.png

 

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.
Mariusz Repczynski

 

jama1234 Frequent Visitor
Frequent Visitor

Re: Remove several rows with same id if one value fulfills condition

Thank you, it works perfectly!

Super User
Super User

Re: Remove several rows with same id if one value fulfills condition

Hi @jama1234 

 

Great, happy to help!

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,105)