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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.