Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jeffery24
Helper I
Helper I

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.

Jeffery24_0-1712938752445.png

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.

1 ACCEPTED SOLUTION
m_dekorte
Super User
Super User

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 

View solution in original post

2 REPLIES 2
m_dekorte
Super User
Super User

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 

Hi @m_dekorte 

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors