【案例】用PowerQuery处理父-子层级结构

首先说明的是，这个帖子的思路来源于Ivan Bondarenko。我在遇到此问题无解时google到他的解决办法。经过摸索试验，解决了我的问题。

需要解决的问题

我遇到的问题如下：

上下级单位

我需要构建出下图中的组织机构层级来：

组织机构层级

let
    上下级拆分 = if List.ContainsAll(Table.ColumnNames(原始数据),{"上级单位","下级单位"})  then 
 let
    源 = Append1,
    重命名的列 = Table.RenameColumns(源,{{"上级单位", "Parent"}, {"下级单位", "Child"}}),
    删除的其他列 = Table.SelectColumns(重命名的列,{"Parent", "Child"}),
    自定义1 = Table.InsertRows(删除的其他列,0,{[Parent="煤矿安全生产标准化直通车",Child="煤矿安全生产标准化直通车"]}),
    buffer = Table.Buffer(自定义1),
// Child and Parent must be equal for RootElement
//    RootElement = "Company",
    RootElement = Table.SelectRows( buffer, each [Child] = [Parent] )[Child]{0},

    // Get depth of each child
    fGetDepthLevel = (child as text, optional level_counter as nullable number) =>
        let
            Level = if level_counter = null then 2 else level_counter,
            parent = buffer[Parent]{ List.PositionOf( buffer[Child], child ) },
            res = if parent = RootElement then Level else @fGetDepthLevel( parent, Level + 1 )
        in 
            res,

    buffer_w_HD = Table.Buffer( Table.AddColumn(
                                                Table.AddColumn( buffer, "HierarchyDepth", each 
                                                    if [Child] = [Parent] then 1 
                                                    else fGetDepthLevel( [Child] )
                                              ),
                                    "Level 1", each RootElement ) ),
                                    

    fGetParentByLevel = (child as text, parent_level as number) =>
        let
            // search position of Child and take its Hierarchy Depth
            parent_level_result = buffer_w_HD[HierarchyDepth]{ List.PositionOf( buffer_w_HD[Child], child ) },
            // if it is desired Depth - take this Child
            res = if parent_level_result = parent_level 
                  then child 
            // otherwise - search further
                  else @fGetParentByLevel( buffer_w_HD[Parent]{ List.PositionOf( buffer_w_HD[Child], child ) }, parent_level )                    
        in res,
    
    HierarchyDepth = List.Max( buffer_w_HD[HierarchyDepth] ),

    table_generator_buffered = Table.Buffer( 
           List.Last(
                List.Generate( ()=>
                    [i = 2, T = buffer_w_HD],
                    each [i] <= HierarchyDepth + 1,
                    each [i = [i] + 1,
                          T = Table.AddColumn( [T], "Level " & Text.From( [i] ), each if [HierarchyDepth] >=i-1 then fGetParentByLevel( [Child], i-1 ) else null )  ],
//                            T = Table.AddColumn( [T], "Level " & Text.From( [i] ), each if [HierarchyDepth] >=[i]-1 then fGetParentByLevel( [Child], [i]-1 ) else null )  ],
                    each [T] ) ) ),

    IsLeaf = Table.AddColumn( table_generator_buffered, "IsLeaf", each not List.Contains( table_generator_buffered[Parent], [Child] ) )
in
    IsLeaf

else null,
    #"Reordered Columns" = Table.ReorderColumns(上下级拆分,{"Parent", "Child", "Level 1", "Level 2", "Level 3", "Level 4", "Level 5", "HierarchyDepth", "IsLeaf"})
in
    #"Reordered Columns"

使用这段代码，有两个地方需要注意：
1.必须确保“上级单位”列中的每一个值都出现在“下级单位”列中，否则代码会出错。
2.必须设置根节点，根节点在这个例子中是以下面这段代码来实现的：

自定义1 = Table.InsertRows(删除的其他列,0,{[Parent="煤矿安全生产标准化直通车",Child="煤矿安全生产标准化直通车"]}),

可以看到对根节点来说，parent和child是同一个值。如果根节点已经存在并且在parent列和child列中的值相等，则无需这一步。

那么，如何确保上级单位（parent）的值都出现在下级单位（ child）中呢？我采取了一个暴力办法：将例子中的表格分别拆分为两个查询，一个只包含“上级单位”列，一个只包含“下级单位”列，然后两个合并，保留只出现在“上级单位”这个表中的值。然后这些值的上级单位都设置为根节点（也就是本例中的“矿安全生产标准化直通车”）。再把结果和原始表格合并，就得到了比较完备的上下级单位列表了。之后再运行那段长代码即可。

代码中，"HierarchyDepth"和"IsLeaf"与本例关系不大，我偷懒没有删除。