How to achieve running total with power query?

Question

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 1

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:

http://1drv.ms/1AzPAZp

It's the file: Power Query demo - Running Total

Answer 2

Very easy.

Step 1: Add an index

#"Added Index" = Table.AddIndexColumn(#"**Prior Step**", "Index", 0, 1)

Step 2: Add a running total

#"Added Running Total" = Table.AddColumn(#"Added Index", "Running Total, each List.Sum(List.FirstN(#"Added Index"[**Column to Add Values From**],[Index]+1)))