Power Query 系列 (07) - 添加列

前面几篇博客介绍了 Power Query (简称 PQ) 的数据源和 M 语言的基础知识，现在开始进入数据处理部分。本篇接着介绍 如何在 PQ 中添加列。添加列是很重要的一个操作，在 PQ 的查询编辑器界面，有一个专门【添加列】功能区。在讲解添加列的过程中，我们会逐步介绍一些相关知识点和 PQ 的操作细节。

本示例基于一个考试分数的清单，做两个方面的统计：1）按单科分数计算级别( A/B/C/D); 2) 将语数外的成绩分别作为一列，并计算总分

创建 table

创建一个空查询，进入高级编辑器，在编辑器中输入下面的代码：

let
    scores = {
        [Name="张三", Subject="语文", Score= 98],
        [Name="李四", Subject="语文", Score= 90],
        [Name="张三", Subject="数学", Score= 100],
        [Name="李四", Subject="数学", Score= 87],
        [Name="张三", Subject="英语", Score= 60],
        [Name="李四", Subject="英语", Score= 72]
    },
    source = Table.FromRecords(scores)
in
    source

点击完成按钮，回到查询编辑器，显示区显示如下：

添加索引列

索引列可以看成记录的编号，PQ 默认从 0 开始，也可以选择从 1 开始或者自定义。在 PQ 查询编辑器界面中，切换到功能区【添加列】，找到【索引列】，这是一个下拉框，选择 【从 1】：

这样就添加了一个索引列：

添加索引列背后的 M 语言代码为：

= Table.AddIndexColumn(source, "索引", 1, 1)

在公式栏中将“索引”改为“No.”，并拖到最左边：

添加条件列

因为我们要对数据进行不同的处理，默认的查询名“查询1”不容易区分，我们将“查询1”重命名，并作为数据处理的起点：选中左边“查询1”，右键弹出菜单，重命名为 scoresOriginal。

然后再选中 scoresOriginal，右键菜单，选择“引用”，这样就根据查询 scoresOriginal 创建了一个新的查询，将新查询命名为 scoresLevel。因为是引用，所以当 scoresOriginal 的数据变化时， scoresLevel 的数据也跟着变化。

选中查询 scoresLevel，切换到【添加列】，点击【条件列】：

PQ 弹出对话框，在界面中按下图进行输入：

完成第一个条件后，点击“添加规则”，增加一行，设置第二个条件。用同样的方法添加后面的条件，最后一个条件写在 ELSE 里面：

操作过程的动图如下：

这样就实现了第一个需求：

进入高级编辑器，查看第一个需求步骤的 M 代码，如下：

let
    源 = scoresOriginal,
    已添加条件列 = Table.AddColumn(源, 
    	"Level", 
    	each if [Score] >= 90 then "A" 
    	else if [Score] >= 85 then "B" 
    	else if [Score] >= 60 then "C" 
    	else "D")
in
    已添加条件列

检查一下看自己是不是已经可以看懂了。如果要查看 Table.AddColumn() 函数的帮助，包括参数的含义，有两种办法，方法一是查看 Microsoft 的 Docs，比如 Table.AddColumn() 函数的帮助文档。第二种方法是新建一个空查询，在公式栏输入 =Table.AddColumn 不要输入函数后面的括号，然后点击确定。

行转列

另外一种形式添加条件列是行转列，之前我在博客中介绍过 pandas 中如何实现行转列的方法，大家可以参考：

pandas 行转列一种典型输出报表的解决方法

在 PQ 中实现行转列思路类似，操作也比较简单。如果在 Excel 中实现类似的处理，条件复杂的时候则非常困难。

根据查询 scoresOriginal 创建一个引用型的查询，命名为 scoresTotal，在这个查询中进行汇总。切换到【添加列】功能区，点击【条件列】，先增加一列，列名为 "Chinese"，这一列存储学生的语文成绩。注意下面界面中，输出的地方要选择 Score 这一列，而不是输入一个值。

我们选择 Score 列：

看一看动图：

用同样的方法，增加 Math 和 English 两列，完成后的界面如下：

因为最终的输出每个学生为一行，需要的数据进行分组，切换到【转换】功能区，点击【分组依据】：

进入下面的界面，按照界面设置要输出的字段。因为数据中包含“语文”、“数学”和“英语”，所以我们也需要有三个新列：

点击确定后，PQ 显示如下：

添加自定义列

我们还需要计算分数的合计。切换到【添加列】功能区，点击【自定义列】，进入设置自定义列界面。设置新列名为 Total，然后双击选择右边已有的列，设置公式如下：

点击确定按钮，完成第二个需求。完成后，再来看看 M 脚本，应该基本上能看懂。

let
    源 = scoresOriginal,
    已添加条件列 = Table.AddColumn(源, "Chinese", each if [Subject] = "语文" then [Score] else null),
    已添加条件列1 = Table.AddColumn(已添加条件列, "Math", each if [Subject] = "数学" then [Score] else null),
    已添加条件列2 = Table.AddColumn(已添加条件列1, "English", each if [Subject] = "英语" then [Score] else null),
    分组的行 = Table.Group(已添加条件列2, {"Name"}, {{"Chinese", each List.Sum([Chinese]), type number}, {"Math", each List.Sum([Math]), type number}, {"English", each List.Sum([English]), type number}}),
    已添加自定义 = Table.AddColumn(分组的行, "Total", each [Chinese]+[Math]+[English])
in
    已添加自定义

本篇通过一个简单的示例，演示了如何添加索引列、条件列和自定义列这几种不同的操作方法。