The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
34 | |
19 | |
18 | |
16 | |
13 |