关于在Power Query中请加权平均的优化问题

显示全部楼层 · 2020-9-25 01:37:40

各位好，先说一下我的需求：
如何优化我下面的步骤，减少运行时间？

问题描述如下：

我有一个大约20多W行的表（大约30M大小），先和另外一个差不多同样行数的表进行了一次合并查询。得到下表：

现在我需要求加权平均，具体要求是这样：

A列是主键，每个主键都有好几行是一样的；
现在要将A列每个主键整合为1行，对应的，要把同样主键（A列）的N列根据M列的权重算一个加权平均值出来。
举个例子，假如A列有一个值有3行（例如都是“2020/1/1 0:00:00-249A-10“），这3行的M列分别100,200,300，N列分别为1,2,3 ；现在要把这三列整合为1列，并且得到一个这样数值：需求数字=（1*100+2*200+3*300）/(100+200+300)

为了达到上述目的，我的做法是这样的：
1）复制这个表一次；
2）在原表中新增一列K=M*N；
3）原表进行分组，根据A列分组，新增一列X=K列之和
4）在复制表中进行分组，根据A列分组，新增一列Y=N列之和；
6）原表和复制表进行联合查询（根据A列），然后合成的表计算X/Y,也就是我要的加权平均。

这个计算过程没有问题，但是非常的慢，我觉得应该是因为表很大，然后又经历了两次合并查询，两次分组。每次都有将近20W行，但是其中相同A列的最多也就是3-4行，这种计算效率非常低。有什么办法优化一下吗？
经常看到网上说Power Query处理几十万行的数据都没问题，我这里这样算一下，i5的CPU运行一次要好几分钟，每次加一个步骤也好慢。

说明一下：我这里不能使用Power Pivot，因为使用PP处理后的表格只能做数据透视表了，没法做为数据源再放到Power Query里面处理了。
如果使用Power Query实在不合适的话，用什么合适？Access ，Python？

网看天下 · 2020-9-25 01:43:40

按你现在的方法得出的结果的有效列只有A列和平均值那列？
如果是这样，一步分组就可以完美实现呀，调整第三参数就可以了
Table.Group(原表,"A列",{"平均值",each List.Sum(List.Transform(List.Zip({[M],[N]}), (x)=>x{0}*x{1}))/List.Sum([M])})