EXCEL|Power Query之合并查询聚合计算-夜雨聆风

EXCEL|Power Query之合并查询聚合计算

今天我们来学习两个表格之间的合并查询，中间会用到聚合计算。

Power Query学到今天，就会发现如果我们完全不用手动编辑公式，只做选择，那么操作熟练了简直就太方便了。但是，似乎复杂点的操作，就绕不开添加自定义列的操作，而只要用到这个操作，几乎就不可避免地要用到一些常规的M函数的使用。既然绕不开，咱们就在每天的练习里，一点点的把常用函数提出来加强记忆。看的多了，用的多了，自然也就记住了。

今天的内容，一是会用到”#(lf)”这个换行符，这里的lf=line feed(不是if)。二是会用到Table.AggregateTableColumn 这个函数。具体咱们实例中讲解学习吧。

案例如下：见以下2张表格。左表是根据提单号汇总的出柜量；右表是根据订舱号统计出来的柜号，很多柜号是换行显示在一个单元格里的。提单号和订舱号是对应关系。现在要判断，右表每一个订舱号对应的柜量是否跟左表统计出来的提单号对应的柜量一致。

1）把发货情况这张表，导入到查询编辑器里，然后插入自定义列，新列名命名为柜号明细，公式里输入Text.Split([柜号],”#(lf)”),见如下操作。这里就用到了#(lf)。使用#(lf)这个换行符把柜号单元格里的柜号分别拆分成单独行。

2）拆分结果如下图。左边是展开柜号明细之前的效果。右边是展开柜号明细之后的效果。这样就实现了一个单元格显示一个柜号。这样发货情况这张表的操作就完成了。关闭并上载。

3）选择客户统计这张表加入查询编辑器，然后选择合并查询，操作如下图所示。让提单号和订舱号进行匹配，联接种类选择左外部。

4）选择确定后的表格里选择发货情况的左右方向按钮，选择聚合，然后对订舱号进行计数，操作如下图所示。

5）计数结果如下图所示。看这个显示结果是不是挺正常的？但其实不是。如果回看原始表格会发现，第二个提单号在发货情况里是没有柜号的。那么为啥下表计数里会有呢？那是因为，空值参加了计算。所以这个显示是失真的。所以这个时候，我们就需要修改公式了。

6）修改公式，排除空值的计数统计。修改后的结果如下图所示。

原公式：= Table.AggregateTableColumn(合并的查询, “发货情况”, {{“订舱号”, List.Count, “发货情况.订舱号的计数”}})

修改后的公式：= Table.AggregateTableColumn(合并的查询, “发货情况”, {{“订舱号”, List.NonNullCount, “发货情况.订舱号的计数”}})

7）添加自定义列，判断发货情况统计的柜量跟客户统计里的柜量是否一致。操作如下图所示。

8）显示结果如下图所示。筛选为0的，就是发货量一致的。

如果我们公式换成=[柜量]=[发货情况.订舱号的计数]，结果如下图所示。True为一致的，False为不一致的。

下面我们来讲下Table.AggregateTableColumn这个函数的语法。

Table.AggregateTableColumn(源表,”要聚合的嵌套表列名”, // 哪个列里存了子表格 {{“子表列1”, 聚合函数, “新列名1”}, {“子表列2”, 聚合函数, “新列名2”}})

以我们上面使用的这个函数的案例为例，解剖下这个函数的使用：

核心参数说明

第 1 参数：源表

包含 Table 类型嵌套列的主数据表：在我们上面的案例中，这个源表是指执行合并查询的这个表格；
第 2 参数：嵌套列名称

被包裹成子表格的那一列（比如你按订舱号分组后，生成的「发货情况」表列）：在我们上面的案例中，是指发货情况这一带左右方向箭头的Table列。

第 3 参数：聚合配置列表

每一组规则格式：{"子表内的列名", 聚合运算, 聚合后输出的新列名}：在我们上面的案例中，只聚合了一列，就是对订舱号进行聚合计数。

理解了这个函数就会发现，如果只是改函数里的个别参数，那其实也不难的。

好了，今天内容就这些。周末愉快。