Forum Discussion

moizsherwani's avatar
moizsherwani
Icon for Continued Contributor rankContinued Contributor
7 years ago
Solved

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...
Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    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
moizsherwani's avatar
moizsherwani
Icon for Continued Contributor rankContinued Contributor
7 years ago

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

LivioLanzo's avatar
LivioLanzo
Icon for Solution Sage rankSolution Sage
7 years ago

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
      • moizsherwani's avatar
        moizsherwani
        Icon for Continued Contributor rankContinued Contributor
        7 years ago

        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"

  • msksenthil's avatar
    msksenthil
    Icon for Helper III rankHelper III
    4 years ago

    Thanks to moizsherwani and LivioLanzo 

     

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