Finding the last Column Name with populated values

[Jeffery24]

Hi. 

Have been searching for awhile but cannot find a solution.

I'm trying to obtain the column name that has the last populated value in a row.
The dataset is a set of events in sequential order, not date order.

Unfortunately our dataset is column based and not unpivoted ( a restriction by IT).
Below is a snapshot of the data.

I am looking to find the value and name of the last event that is populated

I have managed to obtain the value of the last event and added a column "Custom".

= Table.AddColumn(AddTable, "Custom", each List.Last(List.Select(Record.FieldValues(_), each _ <> null)))

 This works well but I am unable to find a similar formula to return the header name.
E.g. row 1142 should return "RIW_FGO_OFD_Last,
row 1144  should return "REW_FGI_Last.
Is this possible without unpivoting the table?
Many thanks.

[m_dekorte · Accepted solution]

Hi @Jeffery24,

You can give this a go:

= Table.AddColumn(AddTable, "Custom", each Table.Last( Table.SelectRows( Record.ToTable(_), each [Value] <> null ))[Name])

Alternatively, this will do the trick as well

= Table.AddColumn(AddTable, "Custom", each Record.FieldNames(_){List.PositionOf(Record.FieldValues(_), List.Last(List.RemoveNulls(Record.FieldValues(_))))} )

Or implemented as custom function (2 steps)

getFieldName = (r as record) as text =>
    Record.FieldNames(r){List.PositionOf(Record.FieldValues(r), List.Last(List.RemoveNulls(Record.FieldValues(r))))},
InvokeFunction =  Table.AddColumn(AddTable, "Custom", each getFieldName(_) )

I hope this is helpful 

[Jeffery24]

Hi @m_dekorte 

First option works perfectly. You're a legend. Made my week many thanks 🙂