Get the latest non-null value from a list of columns.

[PC20]

There are few columns in data. I want to get the latest non-null value from these columns. 

The NewOutputCol is my output column.

In first row, L1 column has the latest non-null value and therefore, output is "A".

In second row, L2 column has the latest non-null value and therefore, output is "E".

In thirs row, L3 column has the latest non-null value and therefore, output is "Z".

In real data, I have 10 such columns to scan from and over 90,000 rows.

Sample data:

L1	L2	L3	NewOuputCol
A	null	null	A
D	E	null	E
X	null	Z	Z

[danextian · Accepted solution]

Hi @PC20 

In Power Query, you can create this custom column

List.Last(List.Select(Record.FieldValues(_), each _ <> null))

[Greg_Deckler]

@PC20 Probably best if you unpivot your columns and add and index perhaps. Otherwise, maybe this:

Column = 
    VAR __Data = [L1] & "|" & [L2] & "|" & [L3]
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES(1,3,1),
            "__Data", PATHITEM(__Data,[Value],TEXT)
        )
    VAR __Max = MAXX(FILTER(__Table,[__Data] <> BLANK()),[Value])
    VAR __Result = MAXX(FILTER(__Table,[Value] = __Max),[__Data])
RETURN
    __Result