如何通过功率查询实现运行总计? [英] How to achieve running total with power query?
问题描述
我想像以前使用Tableau软件一样对电源查询进行总运行.有没有人有想法,在此先感谢!
I want to do a running total with power query like I did with Tableau software before. Does anyone have ideas, thanks in advance!
推荐答案
很抱歉,这个回答很晚了-这个挑战困扰了我好几个月了.
Apologies for the very late answer - this challenge has been nagging at me for many months.
在论坛和博客上浮动的解决方案很少,但它们似乎都需要自定义M代码的页面.有些还不能满足在组更改时需要重新启动运行总计的常见要求.
There are few solutions floating around forums and blogs but they all seem to need pages of custom M code. Some also cant meet the common requirement of needing to restart the running total when a group changes.
因此,我想到了一种无需编写任何自定义代码即可构建的技术-您只需在Power Query窗口中单击并添加一个非常简单的Custom Column.
So I came up a with a technique you can build without writing any custom code - you just click around in the Power Query window and add one very simple Custom Column.
关键步骤是:
- 添加索引列
- 按定义您的组的列进行分组,并添加所有行"列
- 复制所有行"列
- 展开所有行"列的原始副本和副本(此时,每个组中每个行组合都有叉积"结果)
- 添加自定义列累计",以确定要在运行总额"中包括哪些组合行,例如
[索引]> = [#所有行-Copy.Index"]
- 自定义列累计" = TRUE上的过滤器
- 按原始列分组并求和目标字段的副本
我构建了一个可行的解决方案,您可以从OneDrive中下载并试用:
I built a working solution which you can download from my OneDrive and try out:
它是文件:Power Query演示-总计运行
It's the file: Power Query demo - Running Total
这篇关于如何通过功率查询实现运行总计?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!