Get Latest status from multiple columns

[mglomb]

Considering that I have a table as per below picture, with case number and count of status of each phase of a process.

I want to check each column and bring the name of the status to a "Latest Status Column" (in yellow in my picture).

Whar formula sould I use in Power Query to do that?

[smpa01 · Accepted solution]

@mglomb  it is definitely possible if you have Date as values for Recived, Fixed, Delivered

Measure =
VAR _f1 =
    MAX ( 'Table'[Fixed] )
VAR _f2 =
    MAX ( 'Table'[Received] )
VAR _f3 =
    MAX ( 'Table'[Delivered] )
VAR _f4 =
    MAX ( MAX ( _f1, _f2 ), _f3 )
VAR _f6 =
    MAX ( 'Table'[Case] )
VAR _t =
    UNION (
        ADDCOLUMNS (
            SELECTCOLUMNS ( 'Table', "Case", 'Table'[Case], "Date", 'Table'[Fixed] ),
            "Attribute", "Fixed"
        ),
        ADDCOLUMNS (
            SELECTCOLUMNS ( 'Table', "Case", 'Table'[Case], "Date", 'Table'[Delivered] ),
            "Attribute", "Delivered"
        ),
        ADDCOLUMNS (
            SELECTCOLUMNS ( 'Table', "Case", 'Table'[Case], "Date", 'Table'[Received] ),
            "Attribute", "Received"
        )
    )
RETURN
    MAXX ( FILTER ( _t, [Case] = _f6 && [Date] = _f4 ), [Attribute] )

pbix is attached

[jdbuchanan71]

You can add a custom column like this.

= Table.AddColumn(#"Changed Type", "Actual Status", each if [Delievered] = 1 then "Delivered" else 
if [Fix] = 1 then "Fixed" else 
if [Received] = 1 then "Received" else "Other")

You can do this with the conditional column tool as well.

[jppv20]

Hi @mglomb ,

You can use this formula in PowerQuery:

Actual status =

if [Delivered] = 1 then "Delivered" else
if [Fix] = 1 then "Fixed" else
if [Received] = 1 then "Received" else null

If I answered your question, please mark it as a solution to help other members find it more quickly.