A while back I got an email from someone who had taken my Power Query course online.  They were asking how to create a running total, although with some added twists and turns for calculating taxable gains and losses for a stock portfolio.  I decided to tackle that using the List.Accumulate() function.

Now, to be fair, I'm not going to demo the whole stock portfolio thing, but I do want to look at the List.Accumulate function as I found this a bit… confusing… to build.  It's super useful to be sure, but the help article… it needs work.

The Data

I'm using a pretty simple dialog box, inspired by my time in Australia.  You can download a copy from this link, but here's what it looks like:

Pretty simple, but now I want to create a running total that has 685 for Tim Tams, 741 for Stuffed Koala, and so on.

The List.Accumulate Function

So I headed over to MSDN, and found this helpful little article on the List.Accumulate function. It contains the following information.

Function:

List.Accumulate(list as list, seed as any, accumulator as function) as any

Arguments:

Argument	Description
list	The List to check.
seed	The initial value seed.
accumulator	The value accumulator function.

Example:

// This accumulates the sum of the numbers in the list provided.
List.Accumulate({1, 2, 3, 4, 5}, 0, (state, current) => state + current) equals 15

Using the List.Accumulate Function

So this formula looks pretty promising.  Let's go see how it works…

• • Click in the table of data –> create a new query –> From Table
  • Go to Add Column –> Add Custom Column
    • Formula Name:  Initial
    • Formula:

=List.Accumulate(
#"Changed Type"[Sales],
0,
(state, current) => state + current
)

The tricky part here is the #"Changed Type"[Sales], which provides the list of the sales values from the Changed Type step of the query (that was automatically created when we pulled the data in.)

And the result:

So this is a bit weird, as it shows the total for all rows, rather than the running total.  I figured that you should be able to change the accumulator function… except that there is no documentation about what the options are!  (I left some critical feedback on the MSDN site, and would suggest you do too, as that's pretty poor.)

At any rate, I tried dropping the "+ current" from the end, leaving just => state.  The result was a 0 value all the way down the column.  So that plainly didn't work. Then I tried modifying the formula again, leaving => current instead.  The result was 231 on all rows (so the last value in the accumulator.)  How 0 + 231 = 1095 I'm not quite sure but whatever.  state + current returns the overall total.

So plainly, we can't just use this function on it's own.

We need the List.Range function!

With the List.Accumulate function returning a total of all rows fed into it, it became plain that we needed to control what was being fed into the list used as a parameter.  So I reached back out to MSDN and browsed the site until I located the List.Range function.

Function:

List.Range(list as list, offset as number, optional count as number) as list

Arguments:

Argument	Description
list	The List to check.
offset	The index to start at.
optional count	Count of items to return.

Example:

List.Range({1..10},3,5) equals {4,5,6,7,8}

Using the List.Range function

In order to use the List.Range function, we are going to need to figure out which rows we want.  To do that, we need to add an Index column

• Add Column –> Add Index Column –> From 1

Then add a column that makes use of List.Range()

• • Go to Add Column –> Add Custom Column
    • Formula Name:  Initial
    • Formula:

=List.Range(#"Added Index"[Sales],0,[Index])

So what I'm doing here is feeding in the Added Index step (from adding the Index column), and providing the [Sales] column to get a list.  But I'm asking it to return the list for the number of rows as contained in the [Index] column.  The result is a green word that says List all the way down the column.  But if I select the whitespace beside any of those List items, we can see what it is contained within.  Shown below is the list for the Stuffed Koala row:

Okay, so we now have a list of what we need…

Putting it all together

The final step is to put these together.  So let's add a new column again, but this time we'll use that List.Range() function instead of #"Changed Type"[Sales] as shown below

• • Go to Add Column –> Add Custom Column
    • Formula Name:  Success
    • Formula:

List.Accumulate(
List.Range(#"Added Index"[Sales],0,[Index]),
0,
(state, current) => state + current
)

And the result gives us what we were originally looking for:

The only thing left to do is remove the columns we used along the way.  Of course, we could just remove those steps, as they never really needed to happen, but I'm going to select them and remove them so that you can see the work in progress.

And sure enough, we get what we need!