|
HI，大家好，我是星光。 分组聚合是数据处理与分析过程中最常用的技能之一，大家对此也并不陌生，比如将成绩按班级分组求平均，将工资按部门分组汇总等等。 正因为这家伙太过常用，因此各类软件或语言大都提供了专门且高效的功能或语句，典型如Excel的分类汇总和数据透视表…… 打个响指，言归正传，咱们上期聊了SQL的分组聚合语句，这期再聊一下PQ的。 我举个例子，有份成绩表，如下图所示，包含了班级、小组、姓名、科目和成绩等字段。现在需要使用PowerQuery统计每个班级的综合平均分。 首先将数据加载到PowerQuery编辑器。 在PQ编辑器的[主页]选项卡下，单击[分组依据]命令，打开对话框。 分组字段选择'班级'，新列名输入为'平均分'，操作类型选择'平均值'，柱（也就是聚合运算的字段）选择'成绩'。 确定后返回结果如下▼  如果我们需要查询不同班级不同小组的总分以及平均分，只需要添加相关分组和聚合字段即可。 在分组对话框内，选中[高级]单选按钮。单击[添加分组]命令，将分组字段设置为'班级'和'小组'。单击'添加聚合'命令，分别设置新列名、聚合方式以及聚合字段。  确定后返回结果如下：  看到这儿，有些朋友可能会困惑，同样的功能使用Excel透视表就可以轻松解决了，为什么还要学习PQ的分组功能呢？ …… 你最帅，但你这想法不大对。 这事主要有两个原因。一个是PQ分组后的结果表可以嵌套在其它步骤表中继续使用；另外一个是PQ的聚合方式支持函数运算，可以解决很多不单纯的问题，比如在分组结果中删除特定值、去除重复值、合并同类项等。此外，值得一提的是，它还有一个独特的分组方式，按连续值分组。 我举几个小例子。 第1个例子 分组再查询 依然以上图所示的成绩表为例，现在我们需要查询个别班级和小组的总分及平均分数据，查询名单如下▼ 这查询名单随时可能发生变动，也就是说班级和小组可能增、改、删……因此查询结果必须是动态的。 操作步骤如下▼ 首先将A:B列的数据以[自表格/区域]的方式加载到PQ编辑器。 然后将成绩表的数据按之前咱们所分享的方式分组聚合，得出结果如下： 最后切换到查询表，在[主页]选项卡下单击[合并查询]。在打开的对话框中，主表设置为'查询表'，匹配表设置为'成绩表'，匹配字段分别设置为'班级'和'小组'，联结种类为：左外部第1个中的所有行第2个中的匹配行，也就是返回第1张表的所有记录以及第2张表与之相匹配的数据。  确定后返回结果如下▼  单击[成绩表]字段右侧的扩展按钮，在弹出的菜单中去掉和主表字段重复的'班级'和'小组'，同时取消勾选【使用原始列名作为前缀】并确定，即可获取查询结果。  将查询结果上载到Excel工作表，如果查询名单发生了变更，只需要刷新即可。 第2个例子 聚合去重文本值 依然以上文的成绩表为例，现在需要查询各个班级不重复的人员名单并聚合展示，模拟结果如下图所示。
将成绩表数据加载到PQ编辑器，添加新步骤后，在编辑栏输入以下函数即可。 = Table.Group(源, {'班级'}, {'人名', each Text.Combine(List.Distinct(_[姓名]),',')} )这是一个标准的Table.Group函数，它的基本语法如下▼ =Table.Group(表,分组字段,{新列名,聚合方式}) 以上述公式为例，来源表名为源，分组字段是'班级'，新列名为'人名'…… 重点是聚合部分。_[姓名]返回源表分组后的姓名列，是一个列表结构。List.Distinct对它去重复，Text.Combine函数再对去重复后的结果以逗号为分隔符合并为一个字符串。 这里涉及到M函数的容器结构、函数式参数、上下文场景等概念，如果没有M函数基础，一时看不懂，摊手，那就不懂，了解一下，混个面熟也是好的。  第3个例子 按连续值分组 通常意义上的分组，是对整表相同类型的数据进行归纳，比如把整张表同为2班的数据分为一组等。PQ除了支持这样的全局分组模式外，也支持局部分组，或者说连续值分组。 什么意思呢？ 我举个例子。 如下图所示，A列是中英文混杂的数据，需要将其转换为右图所示的结构，即中文在A列，英文合并在B列。 将A列数据加载到PQ编辑器，新增步骤后在编辑栏输入以下函数即可。 = Table.Group(源, '数据', {'英文',each Text.Combine(List.Skip(_[数据]),',')}, 0, (x,y)=>Number.From(y>'z') )和上一个例子相比，Table.Group函数新增了两个可选的参数。 =Table.Group(表,分组字段,{新列名,聚合方式},分组模式,分组依据) 第4参数分组模式是可选的。如果为1，或省略，则为全局分组；如果为0，则为局部连续值分组。本例为0。 第5参数是一个函数参数。其中x代表真元素，y代表真元素以下的其它元素。以本例而言，代码开始运行时，默认第1个元素为真元素，即x为'星光'，y代表'星光'以下的其它元素。 Number.From(y>'z')，判断元素是否为中文，如果是，则返回1，否则返回0。
获取分组结果后，用List.Skip函数删除分组列表首个元素，再用Text.Combine将分组列表元素以逗号为分隔符合并为一个字符串。 …… 没了，打完收工，左上角点关注，咱们下期再见。 文件下载百度网盘..▼ 需要系统学习Excel，却找不到优质图文教程？学习Excel的过程中遇到疑难问题，却找不到人及时作出解答？加入我的知识星球，这一切都不是问题…… 从0到1、从入门到实战... 兼具图文/视频系统教程 微信群答疑... 技巧、函数、透视表、VBA、PQ、SQL 教程全覆盖，想学什么你就学什么▼ |
|
|
来自： H0ing > 《SQL PQ PI》
