如何在Power Query中基于PATH列创建多级层级列？

操作步骤 #

• 将数据导入Power Query编辑器，选中「父路径」列，点击添加列 > 自定义列，输入公式拆分路径：

  = Text.Split([父路径], "｜")
  

  把新列重命名为「拆分后的路径」。

• 生成部门ID与名称的映射表：在左侧查询面板右键当前查询，选择复制，重命名为「部门映射表」。删除除「部门ID」「部门名称」外的所有列，再点击主页 > 删除重复项，确保每个ID对应唯一名称。

• 返回原查询，再次添加自定义列，将拆分后的ID列表转换为对应部门名称：

  = List.Transform([拆分后的路径], (id) => 
      let 
          matchedRow = Table.SelectRows(部门映射表, each [部门ID] = Number.From(id))
      in 
          if Table.RowCount(matchedRow) > 0 then matchedRow{0}[部门名称] else null
  

  重命名为「层级名称列表」。

• 将列表转为可扩展的记录列：添加自定义列，输入公式：

  = Record.FromList([层级名称列表], List.Transform({1..List.Count([层级名称列表])}, (n) => "层级" & Text.From(n)))
  

  重命名为「层级记录」。

• 点击「层级记录」列右侧的扩展按钮，选择扩展到新列，自动生成「层级1」「层级2」等列。

• 最后删除「拆分后的路径」「层级名称列表」「层级记录」这些中间列，调整列顺序即可得到目标格式。

简化版M代码示例 #

如果觉得分步操作繁琐，可直接替换原查询的M代码（注意把源换成你的数据源名称，分隔符根据实际情况调整）：

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    部门映射表 = Table.Distinct(Table.SelectColumns(源, {"部门ID", "部门名称"})),
    添加拆分路径列 = Table.AddColumn(源, "拆分后的路径", each Text.Split([父路径], "｜")),
    添加层级名称列表 = Table.AddColumn(添加拆分路径列, "层级名称列表", each List.Transform([拆分后的路径], (id) => 
        let 
            matchedRow = Table.SelectRows(部门映射表, each [部门ID] = Number.From(id))
        in 
            if Table.RowCount(matchedRow) > 0 then matchedRow{0}[部门名称] else null)),
    添加层级记录 = Table.AddColumn(添加层级名称列表, "层级记录", each Record.FromList([层级名称列表], List.Transform({1..List.Count([层级名称列表])}, (n) => "层级" & Text.From(n)))),
    扩展层级列 = Table.ExpandRecordColumn(添加层级记录, "层级记录", 
        List.Distinct(List.Combine(Table.Column(添加层级记录, "层级记录"))),
        List.Distinct(List.Combine(Table.Column(添加层级记录, "层级记录")))),
    删除中间列 = Table.RemoveColumns(扩展层级列,{"拆分后的路径", "层级名称列表", "层级记录"})
in
    删除中间列

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