To Show the latest Status based on Columns Value

[JakeandSnake]

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. 

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[JakeandSnake]

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 · Accepted solution]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[JakeandSnake]

Thanks~ It works for me now!