Forum Discussion
Finding the last Column Name with populated values
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.
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
2 Replies
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
