有时候我们需要在报表中进行列之间的运算,例如下面这个例子:
在Excel中,这种计算很常见、很简单,只需要新增一列,把相关的两列相加,然后替换掉原来的“总客户数”列中的数据就可以了,那么在PQ中可以怎么做呢?
方法1:Table.AddColumn
最简单的方法是新增一列,把两列相加,但本例要求计算结果要放在原来的位置,并保持相同的列名称,而PQ中新增的列只能在原表的最右侧,这就需要调换列顺序,而新增列的时候又不能和原列名称重复,这意味着新列必须新重名,比如说把它重名为“总客户数1”,然后删除原来的“总客户数”列,再把“总客户数1”重命名为“总客户数”,方法虽然简单,但很是繁琐,以下是详细步骤。
第1步:添加自定义列,把两列数值相加,同时因为不能与原列重名,所以只好命名为“总客户数1”;
第2步:原来的两列计算之后已不再需要,把它们删除;
第3步:新列顺序要和原来保持一致,需要调整列顺序,在列数不多的情况下还算容易,当列数较多时这一步需要列出所有列名,很繁琐,而且刷新的时候也容易出错,所以需要优化代码,这在另一篇文章中会讲到;
第4步:把“总客户数1”改回为原来的名称“总客户数”,工作完成。
方法2:Table.TransformRows配合Record.TransformFields
PQ中无法把一列数据直接加到另一列上(excel中也不行,都需要另加一列进行计算),但是通过代码分解,分步运算,在运算的过程通过命名数据来达到目的,具体方法为:
第1步:用Table.TransformRows把表转化为记录,然后在记录中修改字段值,来达到列间直接运算的目的:
s1两列相加 = Table.TransformRows(源, each let
总客户数 = [总客户数],
新增客户数 = [新增客户数],
修改字段值 = Record.TransformFields(_, {"总客户数",
each 总客户数+新增客户数})
in 修改字段值),
得到的结果是:
第2步:然后用Table.FromRecords把Records重新转化为表,得到如下结果:
可以看出,新增客户数已经被直接加到总客户数中,不需要再新加列且重命名了。
第3步:然后只需删除已不需要的“新增客户数”列就可以了。
方法3:用Table.CombineColumns直接得出结果
其实在PQ中Table.CombineColumns的用途很广,不只是合并文字列这一点用处,它还可以进行列之间的各种组合运算,比如本例中就可以利用它来进行两列数字的加和运算,并且是直接相加,不用新增不必要的列,只需要写一行简单的代码就可以了;
s1合并的列 = Table.CombineColumns(
源,
{"新增客户数", "总客户数"},
each List.Sum(_),
"总客户数")
处理结果如下:
可以看出,只需一步,只需一步,只需一步,就可得出和前面的方法1和方法2同样的结果,新增客户数被直接加到总客户数上了,然后它就不再保留,直接删掉了。
Table.CombineColumns(
table as table, /* 参数1 */
sourceColumns as list,/* 参数2 */
combiner as function, /* 参数3 */
column as text /* 参数4 */)
as table
分析Table.CombineColumns函数语法,可以看出关键在参数3,在这个例子中,处理的对象是{451, 58}, {372, 87}, {247, 126}这三个List,对于每个List我们都要把其中的两个数字相加,这就是each List.Sum(_)所进行的操作,每一个List都求和,就这么简单。
对比三种方法,可以说各有优势:
- 方法1最好理解,但略显繁琐,在刚开始玩PQ的时候可以用这种方法,但玩了一段时间之后会发现有时候它相当繁琐,效果也不高,这时候可以玩一下方法2:
- 方法2的原理就是先把表转化为记录,在记录里处理好了之后再转化为表,它可以进行表格列之间的各种复杂运算:
- 而方法3的效率是最高的,它一步到位,充分利用了合并列公式的功能,其实它本来的目的就是列间运算,而不仅是合并这么简单,但是它会把多列合并为一列,在需要保留原有各列的情况下还需要对合并之后的列进行展开,在这种情况下可以先增加一列辅助列,复制需要保留的那一列数据,然后把这加在需要合并的那一列上就行了。
好了,PQ中的列间运算就是这样玩的,这是我总结出来的几种玩法,亲爱的读者朋友们学会了吗,当然可用的方法可能不止这三种,如果你有更聪明的玩法,一定要记得告诉我哟,寓教于乐,是最高效的学习方法,乐在其中,使我们能够更充分地享受工作,享受生活。
实例下载:
https://pan.baidu.com/s/1DfzsiXEMzKCcN5Bh48FWxwpan.baidu.com提取码:rndy