Forum Discussion
To Show the latest Status based on Columns Value
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic
Most Valuable ProfessionalHey 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:
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
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicThanks~ It works for me now!