Power Query多级父子关系层级统计与路径生成方案问询

问题说明 #

原始数据表格（含Parent、Child两列）：

Parent  Child
A       AA
AA      ASF
AA      AD
ASF     KI
A       DD
W       FG

需求：为每条记录计算以下字段，避免使用多次n-Join的低效方法：

• Level Up：向上层级数（当前节点到根节点的层级深度）
• Level Down：向下层级数（当前节点到最深子节点的层级深度）
• String：以子节点|父节点|根节点格式呈现的向上连接路径

最终输出表格：

Parent  Child   Level Up    Level Down  String
A       AA      1           3           AA|A
AA      ASF     2           2           ASF|AA|A
AA      AD      2           2           AD|AA|A
ASF     KI      3           1           KI|ASF|AA|A
A       DD      1           1           DD|A
W       FG      1           1           FG|W

实现步骤与M代码 #

1. 加载数据并定义递归函数 #

将原始数据加载到Power Query编辑器后，通过两个递归函数分别处理向上路径/层级、向下层级的计算，替代多次Join操作：

let
    // 加载原始数据（可替换为你的数据源）
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI00jcyMDJWitWJVjKC8lMlGKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, Child = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent", type text}, {"Child", type text}}),
    
    // 递归函数：向上追溯路径与层级
    GetUpwardPath = (currentChild as text) as record =>
        let
            parentRow = Table.SelectRows(#"Changed Type", each [Child] = currentChild),
            hasParent = Table.RowCount(parentRow) > 0
        in
            if hasParent then
                let
                    parent = parentRow{0}[Parent],
                    parentPath = GetUpwardPath(parent)
                in
                    [
                        LevelUp = parentPath[LevelUp] + 1,
                        Path = currentChild & "|" & parentPath[Path]
                    ]
            else
                [
                    LevelUp = 1,
                    Path = currentChild
                ],
    
    // 递归函数：向下计算最深层级
    GetDownwardLevel = (currentParent as text) as number =>
        let
            childRows = Table.SelectRows(#"Changed Type", each [Parent] = currentParent),
            hasChildren = Table.RowCount(childRows) > 0
        in
            if hasChildren then
                List.Max(List.Transform(childRows[Child], (c) => GetDownwardLevel(c))) + 1
            else
                1,
    
    // 添加向上路径与层级字段
    #"Added Upward Info" = Table.AddColumn(#"Changed Type", "Upward", each GetUpwardPath([Child])),
    #"Expanded Upward" = Table.ExpandRecordColumn(#"Added Upward Info", "Upward", {"LevelUp", "Path"}, {"Level Up", "String"}),
    
    // 添加向下层级字段
    #"Added Level Down" = Table.AddColumn(#"Expanded Upward", "Level Down", each GetDownwardLevel([Child])),
    
    // 调整列顺序匹配目标输出
    #"Reordered Columns" = Table.ReorderColumns(#"Added Level Down",{"Parent", "Child", "Level Up", "Level Down", "String"})
in
    #"Reordered Columns"

代码解释 #

• GetUpwardPath：从当前子节点出发，递归向上查找父节点，自动拼接路径并累计层级数，遇到根节点（无父节点）时停止递归。
• GetDownwardLevel：从当前节点出发，递归遍历所有子节点的最深层级，取最大值加1作为当前节点的向下层级，无子女节点时返回1。
• 后续通过添加列、展开记录、调整列顺序，快速生成目标格式的表格。

优势说明 #

递归函数无需预先判断层级深度，代码简洁且能自适应任意深度的层级结构，彻底避免了多次n-Join的繁琐操作，效率更高。

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