Forum Discussion

Jeffery24's avatar
Jeffery24
Helper I
2 years ago
Solved

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 orde...
Need Help
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    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 

m_dekorte's avatar
m_dekorte
Resident Rockstar
2 years ago

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's avatar
Jeffery24
Helper I
2 years ago

Hi m_dekorte 

 

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