Forum Discussion
Creating a custom column in Power BI for lowest level in a hierarchy
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?
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.
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})
4 Replies
- ImkeF
Community Champion
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.
That worked out perfectly, thanks!
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
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})
