Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Fast Power Query Code for getting the lowest level children in Parent/Child Hierarchy

I am in search of m query that adds a column to display all Children at level 0 in a table with Parent/Child relation. I am already using a function but its painfully slow.

****fnParChAllChildren****

let
    Source = (ParChTable as table, ChildKey as text, ParentKey as text) as table =>
let
    AllChildren = Table.AddColumn(ParChTable, "AllChildren", each 
        List.Skip(
             List.Generate(()=>
             [Children={Record.Field(_, ChildKey)}],     // (List of) records as start values
             each List.Count([Children]) > 0,            // condition under which the next loop is execute
             each [ Children= List.Buffer(fnRecColumnAsList(
                                        Table.SelectRows(ParChTable, (ParChTable) => List.Contains([Children], Record.Field(ParChTable, ParentKey))) 
                                        , ChildKey))],   // Executable action
             each [Children])                            // (Selection of) return
         ,1)),                                           // Skip first element in list which is it's own key 
    LstAllChildren = Table.AddColumn(AllChildren, "LstAllChildren", each List.Combine([AllChildren])),
    Level = Table.Buffer(Table.AddColumn(LstAllChildren, "Level", each List.Count([AllChildren]))),
    Indent = Table.AddColumn(Level, "Indent", each List.Max(Level[Level])*4-[Level]*4),
    JustLevel0 = List.Buffer(fnRecColumnAsList(Table.SelectRows(Indent, each [Level]=0), ChildKey)),
    AllChildrenJustLevel0 = Table.AddColumn(Indent, "ChildrenJustLevel0", each List.Intersect({[LstAllChildren], JustLevel0}))
in
    AllChildrenJustLevel0
in
    Source
****fnRecColumnAsList*****

let
    Source = (Table as table, ColumnName as text) as list =>
 Table.ToColumns(Table.SelectColumns(Table, ColumnName)){0}
in
    Source

I forgot where I got it from but it works and I use column AllChildrenJustLevel0 generated by it. Only bad thing is its painfully slow.

  • Anonymous's avatar
    Anonymous
    6 years ago

    I got it to work faster by putting Table.Buffer step just before calling it.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    I got it to work faster by putting Table.Buffer step just before calling it.