Forum Discussion

Afkkek's avatar
Afkkek
Frequent Visitor
6 years ago
Solved

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...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    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's avatar
    ImkeF
    6 years ago

    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's avatar
Afkkek
Frequent Visitor
6 years ago

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's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
6 years ago

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