Power Query列运算的几种方法比较

有时候我们需要在报表中进行列之间的运算,例如下面这个例子:

图01

在Excel中,这种计算很常见、很简单,只需要新增一列,把相关的两列相加,然后替换掉原来的“总客户数”列中的数据就可以了,那么在PQ中可以怎么做呢?

方法1:Table.AddColumn

最简单的方法是新增一列,把两列相加,但本例要求计算结果要放在原来的位置,并保持相同的列名称,而PQ中新增的列只能在原表的最右侧,这就需要调换列顺序,而新增列的时候又不能和原列名称重复,这意味着新列必须新重名,比如说把它重名为“总客户数1”,然后删除原来的“总客户数”列,再把“总客户数1”重命名为“总客户数”,方法虽然简单,但很是繁琐,以下是详细步骤。

第1步:添加自定义列,把两列数值相加,同时因为不能与原列重名,所以只好命名为“总客户数1”。

图02

第2步:原来的两列计算之后已不再需要,把它们删除。

图03

第3步:新列顺序要和原来保持一致,需要调整列顺序,在列数不多的情况下还算容易,当列数较多时这一步需要列出所有列名,很繁琐,而且刷新的时候也容易出错,所以需要优化代码,这在另一篇文章中会讲到。

图04

第4步:把“总客户数1”改回为原来的名称“总客户数”,工作完成。

图05

方法2:Table.TransformRows配合Record.TransformFields

PQ中无法把一列数据直接加到另一列上(excel中也不行,都需要另加一列进行计算),但是通过代码分解,分步运算,在运算的过程通过命名数据来达到目的,具体方法为:

第1步:用Table.TransformRows把表转化为记录,然后在记录中修改字段值,来达到列间直接运算的目的。

    s1两列相加 = Table.TransformRows(源, each let 
      总客户数 = [总客户数], 
      新增客户数 = [新增客户数],
      修改字段值 = Record.TransformFields(_, {"总客户数", 
                                each 总客户数+新增客户数})
      in 修改字段值),

得到的结果是:

图06

第2步:然后用Table.FromRecords把Records重新转化为表,得到如下结果:

图07

可以看出,新增客户数已经被直接加到总客户数中,不需要再新加列且重命名了。

第3步:然后只需删除已不需要的“新增客户数”列就可以了。

方法3:用Table.CombineColumns直接得出结果

其实在PQ中Table.CombineColumns的用途很广,不只是合并文字列这一点用处,它还可以进行列之间的各种组合运算,比如本例中就可以利用它来进行两列数字的加和运算,并且是直接相加,不用新增不必要的列,只需要写一行简单的代码就可以了。

    s1合并的列 = Table.CombineColumns(
      源,
      {"新增客户数", "总客户数"},
      each List.Sum(_), 
      "总客户数")

处理结果如下:

图08

可以看出,只需一步,只需一步,只需一步,就可得出和前面的方法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中的列间运算就是这样玩的,这是我总结出来的几种玩法,亲爱的读者朋友们学会了吗,当然可用的方法可能不止这三种,如果你有更聪明的玩法,一定要记得告诉我哟,寓教于乐,是最高效的学习方法,乐在其中,使我们能够更充分地享受工作,享受生活。

————————————————
版权声明:本文为CSDN博主「weixin_39678304」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://zhuanlan.zhihu.com/p/78677690

作者: Hugh

Welcome to Wan's world~