Solved: Parent-Child Hierarchy - Highest Parent (using Pow... - Page 2

moizsherwani · ‎09-19-2018

Hello PBI Forum,

So my issue is as follow, I have a parent child relationship as given below and I need (within Power Query and not DAX) a way to find out the highest parent of any child

Team ID - Parent ID

1 - null

2 - null

3 - 1

4 - 3

5 - 2

6 - 5

Result should be as follows

Team ID - Highest Parent ID

1 - null

2 - null

3 - 1

4 - 1

5 - 2

6 - 2

So esentially need to go up the tree to the highest parent that exists. I need for this to happen at the Power Query level

Thanks,

Moiz

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

LivioLanzo · ‎09-20-2018

you just need to change the data types then like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Hierarchy"]}[Content],

    ChangedType = Table.TransformColumnTypes(Source,{{"Team ID", type text}, {"Parent ID", type text}}),

    ListTeamID = List.Buffer( ChangedType[Team ID] ),
    ListParentID = List.Buffer( ChangedType[Parent ID] ),


    fnGetHighestParent =  (n as text) as text =>
        let
            PosOfParent = List.PositionOf( ListTeamID, n ),
            ParID = ListParentID{PosOfParent}
         in
            if ParID = null then ListTeamID{PosOfParent} else @fnGetHighestParent(ListParentID{PosOfParent}),


    FinalTable = Table.AddColumn( ChangedType, 
                                  "HighestParent", 
                                  each  fnGetHighestParent( [Team ID] ), 
                                   type text)
    
in
   FinalTable

Did I answer your question correctly? Mark my answer as a solution!

Proud to be a Datanaut!

View solution in original post

Parent-Child Hierarchy - Highest Parent (using Power Query not DAX)

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly