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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Afkkek
Frequent Visitor

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?

 

Custom Column.JPG

2 ACCEPTED SOLUTIONS
ImkeF
Community Champion
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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

ImkeF
Community Champion
Community Champion

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})

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
ImkeF
Community Champion
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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Afkkek
Frequent Visitor

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
Community Champion
Community Champion

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})

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Afkkek
Frequent Visitor

That worked out perfectly, thanks!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.