Running totals in Power Query, using the List.Range function

The List.Range M language function can create running totals – when each row is a summary of it an all rows above.

(To calculate running total in DAX, click here.)

Lets use this table (it must have the Index column):

Use custom column to define the numbers to summarize:

• =List.Range(
     Table_with_index[Price],
     0,
     [Index])

Because:

• =List.Range(
     Table_with_index[Price], – na name of previous step and column used for summarizing
     0, – the start is from first item (Power Query calculate from 0, not from 1…)
     [Index]) – we will get as many numbers from Price, as defined in Index column

Then simply switch the datatype of result column to number:

and group it, using Sum:

Thats all:

Nice, but isn’t it too long?

If you are not afraid of M language, you can use List.Accumulate and do it all in one formula.

The thing is, that List.Range finds the area to calculate, and then List.Accumulate summarize it.

• List.Accumulate(
        List.Range(
              #”Changed Type”[Price],
              0,
              [Index]),
        0,
        (state, current)=>state + current)

Similarly, you can use List.Sum:

• List.Sum(
     List.Range(
        #”Changed Type”[Price],
        0,
        [Index]))