Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Mariusz
Community Champion
Community Champion

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

 

View solution in original post

5 REPLIES 5
Mariusz
Community Champion
Community Champion

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

 

Anonymous
Not applicable

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? 

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.

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

 

Anonymous
Not applicable

Thank you, it works perfectly!

Hi @Anonymous 

 

Great, happy to help!

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors