Creating a custom column in Power BI for lowest level in a hierarchy

[Afkkek]

I'm dealing with a hierarchy in a data cube with various codes with uneven levels which would make custom measures and reporting a nightmare.

The below screenshot is a mockup in Excel just to give a visualization of how the hierarchy looks - would it be possible to create a custom column within the Power BI Query Editor to always pull the lowest item in the hierarchy where available to normalize the levels, like where it reads Custom Column below?

[ImkeF · Accepted solution]

Sure: Just add a column with this formula:

List.Last(List.Select(Record.FieldValues(_), each _ <> null))

It selects the last value from the row's values that is not null.

[ImkeF · Accepted solution]

Sure: Just add a column with this formula:

List.Last(List.Select(Record.FieldValues(_), each _ <> null))

It selects the last value from the row's values that is not null.

[Afkkek]

Actually, is there a way to refine the custom column a bit more to select the last from amongst certain columns?  I just noticed that upon adding additional columns, it would confuse things and start pulling the last column added instead of from the original hierarchy.

[ImkeF · Accepted solution]

Sure: You select the fields first that shall be considered:

List.Last(List.Select(Record.FieldValues(Record.SelectFields(_, {ListOfColumnNames})), each _ <> null))

... unfortunately the latest code editor doesn't allow any custom code highlighting any more, but the addition is happening on the innermost level of the formula: Record.SelectFields(_, {ListOfColumnNames})

[Afkkek]

That worked out perfectly, thanks!