Accumulating lists in Power Query using… List.Acculumate()

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!

4 thoughts on “Accumulating lists in Power Query using… List.Acculumate()

  1. Hi Michael

    Very interesting article!!

    One question :

    Is it possible to insert a max sum value so the accumulator stops/resets

    Let me explain
    I have 1 column with item numbers and 1 column that shows the The length of each item number
    The range of the length for each item number goes from 8 character to 16 charter.
    I want to do running total on length of each item number, but the sum must not exceed 250 characters. If it does the accumulator must reSET

    A 8
    B 18
    Ä 239
    D 247
    E 9
    F 19
    G
    ….

  2. Col 1 = Item numbers
    Col 2 = number of characters in the item number (LENGTH)
    Col 3 = Running total of Col 2

    If running total value in COL 2 >= 60 then state should reset to 0 (Zero)

    3 columns
    Item Length RT
    A 10 10
    B 15 25
    C 20 45
    D 10 55
    ———-
    E 10 10 ( Here the state should reset to zero as the RT >=60) ((55 + 10) > 60 is TRUE)
    F 19 29
    G 15 44
    H 10 54
    ———–
    I 12 12 ( HERE THE STATE SHOULD RESET TO ZERO again AS THE RT >=60 ((54 + 12) > 60 is TRUE)
    Etc…..

Leave a Reply

Your email address will not be published. Required fields are marked *