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
ANP
Frequent Visitor

create multilevel heirarchy table from single table

Hello team, I have a table like 

Team IDTeam NameParent Team ID
1Anull
2Bnull
3C1
4D1
5E3
6F3
7G2
8H2
9I7

 

i load this table dynamically and from this I have to derive below logically ( Team A and B are highest level portfolio hence not appearing in the first column, team C and G are also parent of some team but not highest level hence appearning in the first level ) 

 

TeamParent TeamPortfolio
CCA
DDA
ECA
FCA
GGB
HHB
IGB

Hope the requirement is clear with example ,  I could achieve 2 level(team and its parent) using same table with simple inner join (self) but getting output like above I am facing some or other challange.. can you please help with the power query for this. thanks in advane.

1 REPLY 1
ThxAlot
Super User
Super User

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"

 

ThxAlot_0-1710163164272.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LearnAndPractise(Everyday)


)



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.