Forum Discussion

JakeandSnake's avatar
JakeandSnake
Frequent Visitor
5 years ago
Solved

To Show the latest Status based on Columns Value

Hi Experts,

 

I have one table as below:

Conditions:

1. In reality, I have thousands of projects. More than 500K rows. 

2. I have a lot of steps, could be 30 steps columns.

 

Expectation:

I want to have a column to show the farthest step name with the value "TRUE", something like below. Basically to show the latest status of each project.

Anyone can help? 

Thanks in advance. 

 

Need Help
  • selimovd's avatar
    selimovd
    5 years ago

    Hey JakeandSnake ,

     

    for the example you provided it worked 😉

    Rename the columns to give the step an order and put the number first, then it will work:

     

    And the result:

     

    When you have more than 10 columns make sure you have the same amount of numbers at the beginning everywhere:

     

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     

4 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Hey JakeandSnake ,

     

    that's possible in Power Query. For that unpivot the other columns, remove the FALSE ones and then GROUP BY the MAX of the Status column.

    Check my example:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRCgkKdcWg3Bx9gl2VYnWilZycnOB8XDRInbOzMy7DwBRIjYuLCw6b4CbFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Step 1" = _t, #"Step 2" = _t, #"Step 3" = _t, #"Step 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Step 1", type logical}, {"Step 2", type logical}, {"Step 3", type logical}, {"Step 4", type logical}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = true)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Project"}, {{"MaxStep", each List.Max([Attribute]), type text}})
in
    #"Grouped Rows"

     

    In my example the initial table looks like this:

     

    And the final result will look like this:

     

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • JakeandSnake's avatar
      JakeandSnake
      Frequent Visitor
      5 years ago

      Hi selimovd ,

       

      Thanks for your advice. 

      I assume your last step is to group by the MAX of the "Attribute" column. 

      And I guess it works because the status columns are named sequentially " Step 1", Step 2"...

      However, when I tried mine, which status columns are totally words like "Step ABC", "Step BCD"...The results were not accurate. Any idea? 

      • selimovd's avatar
        selimovd
        Icon for Most Valuable Professional rankMost Valuable Professional
        5 years ago

        Hey JakeandSnake ,

         

        for the example you provided it worked 😉

        Rename the columns to give the step an order and put the number first, then it will work:

         

        And the result:

         

        When you have more than 10 columns make sure you have the same amount of numbers at the beginning everywhere:

         

         

        If you need any help please let me know.
        If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
         
        Best regards
        Denis