首先说明的是，这个帖子的思路来源于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"与本例关系不大，我偷懒没有删除。
