在本系列的[第 14 篇，我介绍了类似 BOM 的数据应该怎么存储，以及在 Power Query 中如何展开。那篇文章使用创建辅助查询的方法，从而简化了在查询编辑器的操作，小白都是可以理解和掌握的。但如果我们来回顾一下处理的过程，有很多重复的操作，比如合并和展开第 2 级数据，合并和展开第 3 级数据等等。有编程概念的人立即想到，应该用循环来解决啊。在编程语言中，循环是一种基础的控制结构，是基础的东西，但在 PQ 中却没有直接的循环语句，需要通过函数的辅助来实现。上篇介绍了 List.Generate 函数之后，我们可以对 BOM 数据的展开进行优化了。
我们先来看看重复的代码：
image
这些代码一共重复了 3 次，如果数据的层级更多，则会有更多的重复。所以，首先考虑将这些重复的代码封装在一个函数中。在 Power Query 中，如果能借助查询编辑器操作的地方，尽量在 UI 中进行操作，没必要手写代码。为了简单，我用查询转换为函数的方式来操作：
新建一个空查询，在高级编辑器中输入下面的代码：
let
Level1Table = Table.SelectRows(Hierarchy, each ([Level] = 1))
in
Level1Table
然后将 Level1 复制为一个新的查询 Level2，将筛选条件改为 Level = 2:
let
Level1Table = Table.SelectRows(Hierarchy, each ([Level] = 1))
in
Level1Table
选中 Level1 查询，与 Level2 查询进行合并查询操作。因为本博客是一个系列，前面有专门文章讲合并操作的查询，就不每次重复贴图了。我们将重点放到 M 语言的代码中来。完成合并查询后的 M 语言代码如下：
let
Level1Table = Table.SelectRows(Hierarchy, each ([Level] = 1)),
MergedWithLevel2 = Table.NestedJoin(
Level1Table, // left table
{"Child"}, // link field in left table
Level2, // right table
{"Parent"}, // link field in right table
"Level2", // column name
JoinKind.LeftOuter),
ExpandedLevel2 = Table.ExpandTableColumn(
MergedWithLevel2,
"Level2", // this column will be expanded
{"Child"}, // old column names
{"Child.1"}) // new column names
in
ExpandedLevel2
经过观察，将下面几个作为参数：
- parentTable：父表，尽管现在连接是根据 level 就可以确定，但后面需要迭代逐步变化，所以作为参数
- linkFieldInParent: 父表的关联字段，子表的关联字段是固定的，总是 Parent
- level: 用于根据 Level 确定 childTable
根据这个思路，将代码稍作变更：
let
parentTable = Table.SelectRows(Hierarchy, each [Level]=1),
linkFieldInParent = "Child",
level = 1,
nextLevel = level + 1,
nextLevelText = Text.From(nextLevel),
childTable = Table.SelectRows(Hierarchy, each ([Level] = nextLevel)),
MergedData = Table.NestedJoin(
parentTable, // left table
{linkFieldInParent}, // link field in left table
childTable, // right table
{"Parent"}, // link field in right table
"Level" & nextLevelText, // column name
JoinKind.LeftOuter),
ExpandedData = Table.ExpandTableColumn(
MergedData,
"Level" & nextLevelText, // this column will be expanded
{"Child"}, // old column names
{"Child" & nextLevelText}) // new column names
in
ExpandedData
回到查询编辑器，没有错误，能够出来正确的结果，就可以将查询转换成函数了：
(
parentTable as table,
linkFieldInParent as text,
level as number
) as table =>
let
nextLevel = level + 1,
nextLevelText = Text.From(nextLevel),
ChildTable = Table.SelectRows(Hierarchy, each [Level] = nextLevel),
MergedData = Table.NestedJoin(
parentTable,
{linkFieldInParent},
ChildTable,
{"Parent"},
"Level" & nextLevelText,
JoinKind.LeftOuter),
ExpandedData = Table.ExpandTableColumn(
MergedData,
"Level" & nextLevelText,
{"Child"},
{"Child" & nextLevelText})
in
ExpandedData
将查询命名为 fnMergeChild，在查询编辑器中测试函数的功能。
image
这样就可以得到 Level2:
image
再对 Level2 调用 fnMergeChild 函数:
image
这样就得到了 level3:
image
测试没有问题，就可以创建查询来实现展开所有层级了。因为操作是类似的，这里直接给出代码：
let
Source = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
ChangedTypes = Table.TransformColumnTypes(
Source,
{{"Child", type text},
{"Level", Int64.Type},
{"Parent", type text}}),
// max level
maxLevel = List.Max(ChangedTypes[Level]),
// select level 1
Level1Table = Table.SelectRows(ChangedTypes, each ([Level] = 1)),
// call fnMergeChild function 3 times
TableList = List.Generate(
() => [tbl=Level1Table, linkField="Child", level=1],
each _[level] <= maxLevel,
each [
tbl = fnMergeChild([tbl], [linkField], [level]),
linkField = "Child" & Text.From(level),
level=[level]+1
],
each [tbl]
),
LastTable = List.Last(TableList),
RemoveUnrelated = Table.RemoveColumns(LastTable,{"Level", "Parent"})
in
RemoveUnrelated
网友评论