List.Accumulate is a very powerful function that is often overlooked, when doing Power Query transformations. Its basic behaviour is explained in the reference. How this function works is accumulates a result from a specified operation (accumulator function) starting from the initial value – seed –  and going row by row till the end of a specified list. Radacad greatly explained the possibilities of the function. I treat List.Accumulate as a function that lets me access a previous row. Something that is very often expected by customers using Excel sheets as data sources. This enables me to e.g. detect a change in ID or date – from my experience 90% of the cases.

But how do to it? Examples provided by Radacad are great, but I want to truely accumulate lists in Power Query…

Let us go through the syntax basing on an easy example of cummulative sum. In this case I have a simple list with 10 numbers.

To create a cummulative sum using List.Accumulate, I type:

The result is a list with cummulative values:

But first let us go through the syntax:

First part points to the list that we will iterate through. In our case it is a single column with numbers 1 to 10:

Next part is our seed – initial value of the accumulator. In our case {0} – a list with a single value 0:

And finally the accumulator:

For a reference: index – this is the current value of the row in the list you are iterating through, sum – this is the result of the accumulator.

If the current value of the list is the first value of the list, the result of the accumulator is a list with the first value – which is index, thus writing it as {index}. Below line is important, because we do not want the seed to be the first value.We cannot set the seed to the first value, because accumulator will try to add seed to the first value.

Code below states: If index is not the first value of the iterated list, I want to append the list (remember {}) containing one value – List.Last(sum) + index to the result of the accumulator (in the second step, this would be {1}). List.Last(sum) + index means that I want to take the last value of the accumulator and add index to it. This will produce a list with one value e.g.{3} which I will append to the list – resulting in {1,3}. After repeating steps 10 times I will end up with a list of cumulative sums.

The final step is combining the table with the list:

My whole code:

Another example is straight out of the PowerBI forum. The case is simple, create a special index that grows whenever the column with the product changes:

The whole code for the solution:

The line worth mentioning is:

It is very similar to the previous example. Only exception is if else condition which evaluates expression:

Mind that we iterate through index column. This line checks if Category in the current row is the same as in the previous one. If it is, it appends the list with the last value:

If it isn’t, adds 1 to the last value:

Here is my yet another example from the forum.

And that is it! Let me know, what you think if this approach to List.Accumulate()!

Thanks!