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
PattemManohar's avatar
PattemManohar
Icon for Community Champion rankCommunity Champion
7 years ago

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"

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

      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 ? 

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

        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.