How to achieve running total with power query?
I want to do a running total with power query like I did with Tableau software before. Does anyone have ideas, thanks in advance!
Answer
Apologies for the very late answer - this challenge has been nagging at me for many months.
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.
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.
The key steps are:
- Add an Index column
- Group By the column(s) that define your groups, and add an "All Rows" column
- Duplicate that "All Rows" column
- Expand both the original and copy of the "All Rows" column (at this point you have a "cross-product" result of every combination of rows, within each group)
- Add a Custom Column "Cumulative" to determine which of the combination rows to include in the Running Total, e.g.
[Index] >= [#"All Rows - Copy.Index"] - Filter on the Custom Column "Cumulative" = TRUE
- Group By the original columns and Sum the copy of the target field
I built a working solution which you can download from my OneDrive and try out:
It's the file: Power Query demo - Running Total