如何修改Power Query递归代码以适配层级中每个ID对应多ParentId的场景

现有逻辑适配调整 #

你需要对原有递归函数做如下修改，适配单ID对应多ParentID的场景：

• 调用List.PositionOf时传入Occurrence.All可选参数，获取当前ID在列表中所有匹配的位置
• 用List.Transform批量提取所有位置对应的Type、ParentID值
• 递归函数返回值从单文本改为列表类型，支持多匹配结果输出
• 增加去重逻辑，避免层级循环导致的重复计算

修改后的完整代码如下：

let 
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"ID", type text}, {"ParentID", type text}, {"Type", 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, optional processed as list) as list =>
        let
            // 避免循环处理重复ID
            processed = if processed = null then {} else processed,
            // 已处理过的ID直接返回空列表
            skipCheck = List.Contains(processed, n),
            result = if skipCheck then {} else
                let
                    // 获取所有匹配ID的位置
                    Spots = List.PositionOf( ID_List, n, Occurrence.All ),
                    // 筛选当前行中Type匹配的ID
                    matchedCur = List.Distinct(List.Select(List.Transform(Spots, each ID_List{_}), (idx) => Type_List{List.PositionOf(Spots, idx)} = searchfor)),
                    // 提取所有非空父ID
                    Parent_IDs = List.Distinct(List.RemoveNulls(List.Transform(Spots, each ParentID_List{_}))),
                    // 递归处理所有父ID
                    parentResult = if List.Count(Parent_IDs) = 0 then {} else List.Combine(List.Transform(Parent_IDs, each @Highest(_, searchfor, List.Combine({processed, {n}}))))
                in
                    List.Distinct(List.Combine({matchedCur, parentResult}))
        in
            result,

    // 新增列，如果需要返回列表格式可去掉Text.Combine包装
    FinalTable = Table.AddColumn( ChangedType, "StrategyID", each  Text.Combine(Highest( [ID],"Strategy" ), ","), type text),
    FinalTable2 = Table.AddColumn( FinalTable, "SubstrategyID", each  Text.Combine(Highest( [ID],"Substrategy" ), ","), type text),
    // 空值替换
    #"Replaced Errors" = Table.ReplaceErrorValues(FinalTable2, {{"StrategyID", null}, {"SubstrategyID", null}}),
    #"Replaced Empty" = Table.ReplaceValue(#"Replaced Errors","",null,Replacer.ReplaceValue,{"StrategyID", "SubstrategyID"})
in  
    #"Replaced Empty"

更优实现方案（大数据量场景推荐） #

每次遍历全列表查询位置的效率偏低，数据量超过千行时推荐先做ID到行数据的映射，查询效率会大幅提升：

1. 按ID对原表分组，将每个ID对应的所有ParentID、Type值预先存储为列表
2. 递归时直接从映射表读取对应ID的所有父节点，无需重复遍历全列表

代码示例：

let 
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"ID", type text}, {"ParentID", type text}, {"Type", type text}}),
    // 按ID分组构建映射表
    Grouped = Table.Group(ChangedType, "ID", {{"AllRows", each _}}),
    ID_Map = Record.FromList(Grouped[AllRows], Grouped[ID]),

    Highest = (n as text, searchfor as text, optional processed as list) as list =>
        let
            processed = if processed = null then {} else processed,
            skipCheck = List.Contains(processed, n) or not Record.HasFields(ID_Map, n),
            result = if skipCheck then {} else
                let
                    Rows = Record.Field(ID_Map, n),
                    matchedCur = List.Distinct(Table.SelectRows(Rows, each [Type] = searchfor)[ID]),
                    Parent_IDs = List.Distinct(List.RemoveNulls(Rows[ParentID])),
                    parentResult = if List.Count(Parent_IDs) = 0 then {} else List.Combine(List.Transform(Parent_IDs, each @Highest(_, searchfor, List.Combine({processed, {n}}))))
                in
                    List.Distinct(List.Combine({matchedCur, parentResult}))
        in
            result,

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

内容的提问来源于stack exchange，提问作者David McKinney