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

I mean this code from Adam works but with adding " behind "Running Total:

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)))

Answer 2

I think if it is possible in Power Query it would be very complicated. It's just the wrong tool. Use Power Query to bring the data into Power Pivot and create the running total there instead. It is a standard pattern. See this excellent guide: http://www.daxpatterns.com/cumulative-total/

Answer 3

I believe the best technique so far is creating a list and then creating another column using List.Sum. This does not require any complicated codes. Just point and click and will solve the issue.

More details: https://www.sumproduct.com/blog/article/power-query-one-route-to-a-running-total

Answer 4

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 5

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)))

Answer 6

Using Excel 2016,

1. Add Index named "RunningTotal"

2. Go to Query -> Edit -> Add Reference Query (Index must be a column within Source**

3. Go to View -> Advanced Editor and paste the below code to create a function:

4. Rename the query to fnRunningTotal

= (tbl as table, sumcolumn as text, rowindex as number) =>
let
    #"Removed Other Columns" = Table.SelectColumns(tbl,{sumcolumn, "RunningTotal"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [RunningTotal] <= rowindex),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{sumcolumn, "Temp"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {}, {{"FinalRunningTotal", each List.Sum([Temp]), type number}}),
    FinalRunningTotal = Record.Field(#"Grouped Rows"{0},"FinalRunningTotal")
in
    FinalRunningTotal

To use the function:

= Table.AddColumn(Source, "RunningTotalAmount", each fnRunningTotal(Source,"MarketValue",[RunningTotal]))