Power Query 系列 (12) - Power Quer

本篇数据处理来自一个实际处理数据的简化。下图中，假设左边是一个直观的 BOM 结构展示，数据在 Excel 中存储格式如中间部分所示：第一列为物料编码的级别，第二列为物料编码。数据处理任务：需要在 Excel 中增加一列输出物料编码上一级的物料编码(目标为黄色部分)。

image
Excel 处理这种没有明显规律的数据，借助 VBA 比较方便。我的方法是这样的：从第二行开始循环，如果 Level = 1，初始化一个数组 BomMaterials, 如果不等于 1，则根据 level 刷新该级别的物料编码，并且将上级物料的编码写入到 Parent 列。下面的 VBA 代码显示了处理过程。

Public Sub populate_parent()
    Dim currSheet As Worksheet
    Set currSheet = Sheet3
    
    Dim i As Long
    Dim BomMaterials() As String
    Dim level As Integer
    
    For i = 2 To 367 ' 数据开始和结束行
        level = Range("B" & i).Value
        
        If Range("B" & i).Value = 1 Then
            ReDim BomMaterials(1 To 8)
            BomMaterials(level) = Range("C" & i).Value
        Else
            BomMaterials(level) = Range("C" & i).Value
            Range("D" & i).Value = BomMaterials(level - 1)
        End If
    Next
End Sub

现在需要在 Power Query (PQ) 中做相同的处理。将 Excel 工作表的 BOM 数据通过 Ctrl + T 变成 Excel 的 Table，加载到 Power Query 查询编辑器（操作过程可以参考我之前的博客），此时界面如下。为了处理方便，步骤名称我改为了英文。

image
对 BOM查询，添加一个新的条件列，找出每一行物料编码所属的第一级物料编码：
image
image
点击确定按钮，回到查询编辑器，此时界面如下。除了第 1 级，其他级别物料编码为 null。 image

选中 Level1 列，切换到【转换】选项卡，打开【填充】下拉框，选择向下填充。或者在选中这一列后，右键菜单，也有【填充】菜单项。向下填充后，所有 null 值都被自动填充为上一行的值，直到遇到非空值。

image

选中左边 BOM 查询，右键菜单【复制】，将 BOM 复制为一个新的查询，将新查询名改为 BOMGrouped。目前 BOMGrouped 查询和 BOM 查询完全相同。

image

选择 BOMGrouped 查询，选中 Level1 这一列，通过【主页】或者右键菜单中的【分组依据】，打开下面的对话框：

image
注意这里选择：所有行（要点）。
image

通过这种分组的方式，除第一级物料编码之外的所有数据，全部变成一列。Grouped 这一列中的每个单元格，不是一个单值数据，而是 Table 类型的数据，所以将其称为结构化列。我们后面可以对结构化列进行展开 (expand)，获得我们想要的数据，这种数据处理方法跟 Excel 有着天壤之别，后面我们还将通过其它示例，领会和掌握结构化列的使用方法。结构化列不仅仅是 Table 类型，如果某一列是 Table、Record 或者 List，则都是结构化列。

image

完成本步骤，BOMGrouped 查询的 M语言脚本如下：

let    
    Source = Excel.CurrentWorkbook(){[Name="BOM"]}[Content],
    ChangedTypes = Table.TransformColumnTypes(Source,
        {{"IDX", Int64.Type}, {"Level", Int64.Type}, {"MaterialCode", type text}}),
    AddedLevel1 = Table.AddColumn(ChangedTypes, "Level1", each if [Level] = 1 then [MaterialCode] else null),
    FilledDown = Table.FillDown(AddedLevel1,{"Level1"}),
    GroupedByLevel1 = Table.Group(FilledDown, {"Level1"}, 
        {{"Grouped", each _, type table [IDX=number, Level=number, MaterialCode=text, Level1=text]}})
in
    GroupedByLevel1

接下来，选中 BOM 查询，与刚才加工好的 BOMGrouped 查询做一个合并查询 (操作方法可以参考本系列第 9 和第 10 篇 ）：

image
完成本步骤后界面如下： image
需要对 BOMGrouped 列进行展开操作，展开时只保留 Grouped 字段： image

完成操作后，点击 Grouped 字段，下面已经可以看到 BOM 信息，是 Table 类型的结构化数据：

image

目前这个子表 (sub-table) 包含所有数据，我们需要对其进行筛选。为了方便处理，我们添加一列，值与 Grouped 一样：

image

完成刚才的步骤，高级编辑器中的 M 代码为：

let    
    Source = Excel.CurrentWorkbook(){[Name="BOM"]}[Content],
    ChangedTypes = Table.TransformColumnTypes(Source,{{"IDX", Int64.Type}, {"Level", Int64.Type}, {"MaterialCode", type text}}),
    AddedLevel1 = Table.AddColumn(ChangedTypes, "Level1", each if [Level] = 1 then [MaterialCode] else null),
    FilledDown = Table.FillDown(AddedLevel1,{"Level1"}),
    MergedWithBOMGrouped = Table.NestedJoin(FilledDown, {"Level1"}, BOMGrouped, {"Level1"}, "BOMGrouped", JoinKind.LeftOuter),
    ExpandedBOMGrouped = Table.ExpandTableColumn(MergedWithBOMGrouped, "BOMGrouped", {"Grouped"}, {"Grouped"}),
    AddedSelectedMaterialCol = Table.AddColumn(ExpandedBOMGrouped, "SelectedMaterial", each [Grouped])
in
    AddedSelectedMaterialCol

为了对 Grouped 列包含的字表数据进行筛选，需要用到两个标准款函数：Table.SelectRows 和 Table.Last。

Table.SelectRows(table as table, condition as function) as table

Table.SelectRows 函数根据条件筛选出表中符合条件的记录，返回值是 table

Table.Last(table as table, optional default as any) as any

Table.Last 函数获取 table 中的最后一行，返回值一般是 record。

使用上面两个函数，对最后一个步骤的代码进行变更，变更之前：

AddedSelectedMaterialCol = Table.AddColumn(ExpandedBOMGrouped, "SelectedMaterial", each [Grouped])

首先对 subtable 进行筛选，筛选出 IDX 比主表 IDX 小，物料的 Level 比本 Level 小 1 的所有记录：

AddedSelectedMaterialCol = Table.AddColumn(ExpandedBOMGrouped, "SelectedMaterial", 
    each Table.SelectRows([Grouped], (x) => x[IDX] <= [IDX] and x[Level] = [Level]-1))

回到查询编辑器界面，子表的筛选条件已经起作用了：

image

然后再对最后一个步骤的代码用 Table.Last 函数，将符合条件的记录从 Table 变成 Record，经这一处理，字表只剩下最后一行符合条件的记录，单元格的数据类型是 Record：

AddedSelectedMaterialCol = Table.AddColumn(ExpandedBOMGrouped, "SelectedMaterial", 
        each Table.Last(
        Table.SelectRows([Grouped], 
        (x) => x[IDX] <= [IDX] and x[Level] = [Level]-1)))

完成这一个步骤，界面如下：

image

M 语言脚本如下：

let    
    Source = Excel.CurrentWorkbook(){[Name="BOM"]}[Content],
    ChangedTypes = Table.TransformColumnTypes(Source,{{"IDX", Int64.Type}, {"Level", Int64.Type}, {"MaterialCode", type text}}),
    AddedLevel1 = Table.AddColumn(ChangedTypes, "Level1", each if [Level] = 1 then [MaterialCode] else null),
    FilledDown = Table.FillDown(AddedLevel1,{"Level1"}),
    MergedWithBOMGrouped = Table.NestedJoin(FilledDown, {"Level1"}, BOMGrouped, {"Level1"}, "BOMGrouped", JoinKind.LeftOuter),
    ExpandedBOMGrouped = Table.ExpandTableColumn(MergedWithBOMGrouped, "BOMGrouped", {"Grouped"}, {"Grouped"}),
    AddedSelectedMaterialCol = Table.AddColumn(ExpandedBOMGrouped, "SelectedMaterial", 
        each Table.Last(Table.SelectRows([Grouped], (x) => x[IDX] <= [IDX] and x[Level] = [Level]-1)))
in
    AddedSelectedMaterialCol

读者也可将 M 语言的脚本拷贝到高级编辑器，直接看效果，并且可以进入每一步骤查看数据的变化过程。最后，对 SelectedMaterial 结构化列展开，只保留 MaterialCode 这一列。

image

将列名改为 Parent，并删除不需要的辅助列。完成后的 M 语言脚本：

let    
    Source = Excel.CurrentWorkbook(){[Name="BOM"]}[Content],
    ChangedTypes = Table.TransformColumnTypes(Source,{{"IDX", Int64.Type}, {"Level", Int64.Type}, {"MaterialCode", type text}}),
    AddedLevel1 = Table.AddColumn(ChangedTypes, "Level1", each if [Level] = 1 then [MaterialCode] else null),
    FilledDown = Table.FillDown(AddedLevel1,{"Level1"}),
    MergedWithBOMGrouped = Table.NestedJoin(FilledDown, {"Level1"}, BOMGrouped, {"Level1"}, "BOMGrouped", JoinKind.LeftOuter),
    ExpandedBOMGrouped = Table.ExpandTableColumn(MergedWithBOMGrouped, "BOMGrouped", {"Grouped"}, {"Grouped"}),
    AddedSelectedMaterialCol = Table.AddColumn(ExpandedBOMGrouped, "SelectedMaterial", 
        each Table.Last(Table.SelectRows([Grouped], (x) => x[IDX] <= [IDX] and x[Level] = [Level]-1))),
    ExpandedMaterialCol = Table.ExpandRecordColumn(AddedSelectedMaterialCol, "SelectedMaterial", {"MaterialCode"}, {"Parent"}),
    DeletedCols = Table.RemoveColumns(ExpandedMaterialCol,{"Level1", "Grouped"})
in
    DeletedCols

通过【主页】上关闭并上载，将结果数据加载到 Excel 工作表：

image
示例数据已经放到 github - BOMSample.xlsx，方便大家学习。