Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
moizsherwani
Continued Contributor
Continued Contributor

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

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.
1 ACCEPTED SOLUTION

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

14 REPLIES 14
PattemManohar
Community Champion
Community Champion

I tried below and it worked out fine..

 

1. Create an Alias/Duplicate table for the main table

2. Use Merge Queries to merge both Main & Duplicate table joining ParentID in Duplicate table with TeamID in Main table (Left Outer Join).

3. You can see a tabular new field, expand it.

4. Add a conditional column, to have ParentID from Main table in case it is NULL in Duplicate table

 

Here is the code for the above steps:

 

TableName : HighParent

DuplicateTableName : HighParentDup

 

let
Source = Table.NestedJoin(HighParentDup,{"ParentID"},HighParent,{"TeamID"},"HighParent",JoinKind.LeftOuter),
#"Expanded HighParent" = Table.ExpandTableColumn(Source, "HighParent", {"TeamID", "ParentID"}, {"HighParent.TeamID", "HighParent.ParentID"}),
#"Sorted Rows" = Table.Sort(#"Expanded HighParent",{{"TeamID", Order.Ascending}}),
#"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "FinalParent", each if [HighParent.ParentID] = null then [ParentID] else [HighParent.ParentID]),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"HighParent.TeamID", "HighParent.ParentID"})
in
#"Removed Columns"





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

Proud to be a PBI Community Champion




Thanks for your reply. The issue is that unlike the example in reality there maybe upto 6 levels of children so your solution may not be feasible. Thoughts?
Thanks,

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

@moizsherwani As far as I know, my code should work on your real dataset

 

could you post a dataset on which it does not work ? 

 


 


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


Proud to be a Datanaut!  

@LivioLanzo sorry I should have mentioned I was referring to the problem in the solution as proposed by @PattemManohar

 

@LivioLanzo I will test your solution and get back to you.

Thanks,

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

Hi @LivioLanzo so your query does not work because I believe you are checking the numbers for the smallest number whereas in fact my team names are not really numbers but as follows

 

Team ID - Parent ID - Higest Parent

B1            - null       - B1 (because it is null)

B2            -  null       - B2 (because it is null)

T1            - B1          - B1

T2            - T1          - B1

T3            - B2          - B2

T4            - T3           - B2

 

Removing the B or the T is also not a solution as there is a B1 and a T1, we need to climb up the hierarchy

 

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.

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!  

@LivioLanzo  Awesome, thank you!

Thanks to @moizsherwani and @LivioLanzo 

 

But when we using the same for larger dataset like 32k rows it shows the below error 

msksenthil_0-1656221260479.png

 

@LivioLanzo Get an error

 

 

fnGetHighestParent Error 1.png 

Thanks,

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

Here is the link to an excel file with the teams

 

https://we.tl/t-eYzSX33BO9

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 I managed to fix that error but get an error on the final step now saying

 

"Expression.Error: We cannot convert a value of type List to type Table.
Details:
Value=List
Type=Type"

Thanks,

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

@moizsherwani Are you pasting the code exactly as it is? If not, what is the complete M code? 

 


 


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


Proud to be a Datanaut!  

@LivioLanzo I realized I was making a mistake with the code. The solution works perfectly. Thanks so much.

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
Solution Sage
Solution Sage

Try this (your table name is Hierarchy):

 

 

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

    ChangedType = Table.TransformColumnTypes(Source,{{"Team ID", Int64.Type}, {"Parent ID", Int64.Type}}),

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


    fnGetHighestParent =  (n as number) as number =>
        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 if 
                                            [Parent ID] = null 
                                        then 
                                            null 
                                        else 
                                             fnGetHighestParent( [Team ID] ), 
                                   type number )
    
in
   FinalTable

 


 


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


Proud to be a Datanaut!  

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors