How can I write a recursive query in Power Query to traverse up a tree

Question

Newbie question: I have a table with ID, ParentID, and Type. I want to create two new columns (StrategyID, SubstrategyID) that contains the ID for the row if its Type = 'Strategy' or 'Substrategy'. Otherwise, I want to look at its parent row and return that ID if it matches the Types sought. If not, repeat and look at the parent of the parent, etc. I am not getting the syntax for functions in general and recursive functions in particular in PowerQuery.

I've looked at many examples and videos, and found some help, but not specifically for what I am trying to do.

------------------------------------------------------------
| Existing columns              New Colums                 |
------------------------------------------------------------
| ID | ParentID | Type        | StrategyID | SubstrategyID |
| 1  | 0        | Strategy    | 1          |               |
| 2  | 1        | Substrategy | 1          | 2             |
| 3  | 2        | Feature     | 1          | 2             |
| 4  | 3        | Story       | 1          | 2             |
| 5  | 3        | Story       | 1          | 2             |
| 6  | 1        | Substrategy | 1          | 6             |
| 7  | 6        | Feature     | 1          | 6             |
| 8  | 7        | Story       | 1          | 6             |
| 9  | 7        | Story       | 1          | 6             |
| 10 | 0        | Strategy    | 10         |               |
| 11 | 10       | Substrategy | 10         | 11            |
| 12 | 11       | Feature     | 10         | 11            |
| 13 | 12       | Story       | 10         | 11            |
| 14 | 12       | Story       | 10         | 11            |
| 15 | 12       | Story       | 10         | 11            |
| 16 | 10       | Substrategy | 10         | 16            |
| 17 | 16       | Feature     | 10         | 16            |
| 18 | 17       | Story       | 10         | 16            |
| 19 | 17       | Story       | 10         | 16            |
------------------------------------------------------------
'''

horseyride horseyride · Accepted Answer · 2019-10-07T21:15:09

Give this a try. Assumes source data in Table1 with 3 columns --"ID", "ParentID" and "Type"

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChangedType = Table.TransformColumnTypes(Source,{{"ID", type text}, {"ParentID", type text}}),
ID_List = List.Buffer( ChangedType[ID] ),
ParentID_List = List.Buffer( ChangedType[ParentID] ),
Type_List = List.Buffer( ChangedType[Type] ),

Highest =  (n as text, searchfor as text) as text =>
   let
        Spot = List.PositionOf( ID_List, n ),
        ThisType = Type_List{Spot},
        Parent_ID = ParentID_List{Spot}
     in if Parent_ID = null or ThisType=searchfor then ID_List{Spot} else @Highest(Parent_ID,searchfor),

FinalTable = Table.AddColumn( ChangedType, "StrategyID", each  Highest( [ID],"Strategy" ), type text),
FinalTable2 = Table.AddColumn( FinalTable, "SubstrategyID", each  Highest( [ID],"Substrategy" ), type text),
#"Replaced Errors" = Table.ReplaceErrorValues(FinalTable2, {{"SubstrategyID", null}})
in  #"Replaced Errors"

How can I write a recursive query in Power Query to traverse up a tree

2 Answers