Forum Discussion

ANP's avatar
ANP
Frequent Visitor
2 years ago

create multilevel heirarchy table from single table

Hello team, I have a table like  Team ID Team Name Parent Team ID 1 A null 2 B null 3 C 1 4 D 1 5 E 3 6 F 3 7 G 2 8 H 2 9 I 7   i load this tab...
Need Help
Tips and Tricks
ThxAlot's avatar
ThxAlot
Icon for Super User rankSuper User
2 years ago

A very fundamental use case of recursive function,

 

let
    udf_Ancestor = (parent, ancestor) =>
        let pos = List.PositionOf(IDs, parent)
        in if pos is null or pos=-1 then ancestor else @udf_Ancestor(#"Parent IDs"{pos}, parent),

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pce5DYAwEAXRXn7sxDZgCLmhhtX23wYjS2ww0hszZSWtJE+mArZ/Ktgp9xvQETeik2q/CV1xDd1U+s3oiVvQS03uHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Team ID" = _t, #"Team Name" = _t, #"Parent Team ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team ID", Int64.Type}, {"Team Name", type text}, {"Parent Team ID", Int64.Type}}),
    IDs = #"Changed Type"[Team ID],
    #"Parent IDs" = #"Changed Type"[Parent Team ID],
    #"Invoked udf_Ancestor" = Table.CombineColumns(#"Changed Type", {"Team ID","Parent Team ID"}, each udf_Ancestor(_{1}, _{0}), "Portfolio")
in
    #"Invoked udf_Ancestor"