Power Query 系列 (14) - BOM数据展开应用案

层次化数据是一种比较常见的数据关系，比如 BOM、公司的组织架构、族谱等等。本文讲解应该如何对层次化数据进行存储和加工输出。设计的场景如下：

image

为了直观，用 1 位数表示第一级，2 位数表示第 2 级，依次类推。将左边的数据放在 Excel 工作表中，通过 Ctrl + T 变成表，然后加载到 Power Query，将查询命名为 Hierarchy。示例数据已经上传到 Github，方便大家对照学习。

image

将查询 Hierarchy 复制为一个新的查询，改名为 Level1，筛选出 Level 为 1 的数据：

image

查询 Level1 对应的 M 语言脚本为：

let
    Source = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    ChangedTypes = Table.TransformColumnTypes(
        Source,
        {{"Child", type text}, {"Level", Int64.Type}, {"Parent", type text}}),
    Level1 = Table.SelectRows(ChangedTypes, each ([Level] = 1))
in
    Level1

用相同的方法，从 Hierarchy 查询复制出查询 Level2、Level3 和 Level4，分别筛选出 Level 字段为 2、3、4 的数据。Level 1 到 Level 4 四个查询作为数据加工的辅助。

然后选中查询 Level1，与 查询 Level2 进行合并查询操作。要点是选中 Level1 的 Child 字段与 Level2 的 Parent 字段进行匹配。表示第 1 级的 Child 是第 2 级的 Parent:

image
点击确定按钮后的查询编辑器界面如下： image
对 Level2 结构化字段进行展开操作，保留 Child 字段：
image
点击确定按钮后，查询编辑器界面如下： image

完成此步骤后的 M 语言脚本如下：

let
    Source = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    ChangedTypes = Table.TransformColumnTypes(
        Source,
        {{"Child", type text}, 
        {"Level", Int64.Type}, 
        {"Parent", type text}}),
    Level1 = Table.SelectRows(ChangedTypes, each ([Level] = 1)),

    // merged with level1 table
    MergedLevel1Table = Table.NestedJoin(
        Level1, {"Child"}, 
        Level2, {"Parent"}, 
        "Level2", 
        JoinKind.LeftOuter),

    // expanded level 2
    ExpandedLevel2 = Table.ExpandTableColumn(MergedLevel1Table, "Level2", {"Child"}, {"Level2"})
in
    ExpandedLevel2

为了方便，我先创建了一个名为 Level2 的查询，现在这个查询也可以在 M 脚本中编写：

Level2 = Table.SelectRows(ChangedTypes, each ([Level] = 2))

变更前：

image

变更后：

image

然后可以删除查询 Level2 这个辅助查询。再用同样的方法与 Level3 和 Level4 进行合并查询再展开。完成后 M 语言脚本如下：

let
    Source = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    ChangedTypes = Table.TransformColumnTypes(
        Source,
        {{"Child", type text}, 
        {"Level", Int64.Type}, 
        {"Parent", type text}}),
    Level1 = Table.SelectRows(ChangedTypes, each ([Level] = 1)),
    Level2 = Table.SelectRows(ChangedTypes, each ([Level] = 2)),
    Level3 = Table.SelectRows(ChangedTypes, each ([Level] = 3)),
    Level4 = Table.SelectRows(ChangedTypes, each ([Level] = 4)),
    
    // merged with level2 table
    MergedLevel2Table = Table.NestedJoin(
        Level1, {"Child"}, 
        Level2, {"Parent"}, 
        "Level2", 
        JoinKind.LeftOuter),

    // expanded level 2
    ExpandedLevel2 = Table.ExpandTableColumn(MergedLevel2Table, "Level2", {"Child"}, {"Level2"}),

    // merged with level3 table
    MergedLevel3Table = Table.NestedJoin(
        ExpandedLevel2, {"Level2"}, 
        Level3, {"Parent"}, "Level3", 
        JoinKind.LeftOuter),

    // expanded level 3
    ExpandedLevel3 = Table.ExpandTableColumn(MergedLevel3Table, "Level3", {"Child"}, {"Level3"}),

    // merged with level4 table
    MergedLevel4Table = Table.NestedJoin(
        ExpandedLevel3, {"Level3"}, 
        Level4, {"Parent"}, "Level4", 
        JoinKind.LeftOuter),

    // expanded level 4
    ExpandedLevel4 = Table.ExpandTableColumn(MergedLevel4Table, "Level4", {"Child"}, {"Level4"})
in
    ExpandedLevel4

示例数据：github - Hierarchy Data.xlsx