none
How to Get Lowest Level Hierarchy Members without knowing number of levels. RRS feed

  • Question

  • I have a hierarchy table as below. I would like to retrieve the lowest level leaf member dynamically without knowing the number of levels. 

    Is there a way to do that in PowerQuery?


    Wednesday, October 19, 2016 3:01 PM

Answers

  • You júst add a column with this formula:

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

    It will convert the values in the current row into a list, filter out null-values and then take the last element of it.


    Imke Feldmann TheBIccountant.com

    • Marked as answer by qshng Wednesday, October 19, 2016 6:41 PM
    Wednesday, October 19, 2016 4:08 PM
    Moderator

All replies

  • You júst add a column with this formula:

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

    It will convert the values in the current row into a list, filter out null-values and then take the last element of it.


    Imke Feldmann TheBIccountant.com

    • Marked as answer by qshng Wednesday, October 19, 2016 6:41 PM
    Wednesday, October 19, 2016 4:08 PM
    Moderator
  • Thank you Imke. I got an error below when adding a new column:

    Expression error: The name '_' wasn't recognized.  Make sure it's spelled correctly.

    It seems to be caused by Record.FieldValues. But I'm not sure how to fix this. Could you help me please?


    • Edited by qshng Wednesday, October 19, 2016 6:12 PM
    Wednesday, October 19, 2016 6:09 PM
  • Hm - cannot see why this wouldn't work.

    If you check the formula in the editor, the full string should look like this:

    Table.AddColumn(Source, "Custom", each List.Last(List.Select(Record.FieldValues(_), each _<> null)))

    and in the UI-editor:


    Imke Feldmann TheBIccountant.com

    Wednesday, October 19, 2016 6:24 PM
    Moderator
  • I got it work this time. You are Awesome! Thanks a lot!
    Wednesday, October 19, 2016 6:41 PM
  • Imke,

    I'm trying add another column after your solution. I want to return the column header of the lowest level item in the row. I have tried a number of ideas, but they do not compute.

    Tuesday, January 10, 2017 1:10 AM
  • This sounds like a total different request to me, check this out:

    ColumnWithLowestValue = Table.AddColumn(NameOfPreviousStep, "ColumnWithLowestValue", each List.Range(Record.FieldNames(_), List.PositionOf(Record.FieldValues(_), List.Min(Record.FieldValues(_))),1){0})


    Imke Feldmann TheBIccountant.com

    Tuesday, January 10, 2017 10:06 AM
    Moderator
  • Try this one below

    ColumnWithLowestValue = Table.AddColumn(SomeYourStep, "HeaderName", each Table.ColumnNames(SomeYourStep){List.NonNullCount(Record.ToList(_))-1})
    Regards

    Tuesday, January 10, 2017 11:17 AM
  • let
        Source = Table.FromRecords( {
        [ CHILD = "P3L35", 1.CHILD = "P4L52", 2.CHILD = "39121110", 3.CHILD = null ],
        [ CHILD = "P3L35", 1.CHILD = "P4L53", 2.CHILD = "P4L5301", 3.CHILD = "39138050" ]
        } ),
        ColumnNames = Table.ColumnNames( Source ),
        #"Added Custom" = Table.AddColumn(Source, "Custom", each let
        List = List.Select(Record.FieldValues(_), each _<> null ),
        Value = List.Last( List ) & "-" & ColumnNames { List.Count( List ) - 1 }
    in 
        Value),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom","Custom",Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv),{"Custom.1", "Custom.2"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", type text}})
    in
        #"Changed Type"
    This might work. :)
    Wednesday, January 11, 2017 2:08 PM