如何在Power Query中编写遍历树的递归查询 [英] How can I write a recursive query in Power Query to traverse up a tree

问题描述

新手问题：我有一个包含ID、ParentID和Type的表。如果该行的Type=‘Strategy’或‘SubStrategy’，我想创建两个包含该行ID的新列(Strategy ID、SubStrategy ID)。否则，我想查看它的父行，如果它与所查找的类型匹配，则返回该ID。如果不是，则重复并查看父级的父级，依此类推。我不了解一般函数的语法，尤其是PowerQuery中的递归函数。

我查看了许多示例和视频，找到了一些帮助，但不是专门针对我要做的事情。

------------------------------------------------------------
| 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            |
------------------------------------------------------------
'''

推荐答案

试一试。假定源数据位于表1中，包含3列--"ID"、"ParentID"和"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"

这篇关于如何在Power Query中编写遍历树的递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！