Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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! :smileyhappy:

Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    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

     

5 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Mariusz
        Community Champion
        7 years ago

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