# 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:

1 |
Cumulative_new = List.Accumulate(#"Changed Type"[Values],{0}, (sum,index) => if index = #"Changed Type"[Values]{0} then {#"Changed Type"[Values]{0}} else sum& {List.Last(sum) + index}) |

The result is a list with cummulative values:

But first let us go through the syntax:

1 |
List.Accumulate(#"Changed Type"[Values],{0}, (sum,index) => if index = #"Changed Type"[Values]{0} then {#"Changed Type"[Values]{0}} else sum& {List.Last(sum) + index}) |

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

1 |
#"Changed Type"[Values] |

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

1 |
{0} |

And finally the accumulator:

1 |
(sum,index) => if index = #"Changed Type"[Values]{0} then {index} else sum& {List.Last(sum) + index}) |

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.

1 |
if index = #"Changed Type"[Values]{0} then {index} |

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.

1 |
else sum& {List.Last(sum) + index}) |

The final step is combining the table with the list:

1 2 3 |
#"Converted to Table" = Table.FromList(Cumulative_new, Splitter.SplitByNothing(), null, null, ExtraValues.Error), //I merge two columns into a table Add_columns= Table.FromColumns(Table.ToColumns( #"Changed Type")&{Cumulative_new}) |

My whole code:

1 2 3 4 5 6 7 8 9 10 11 12 |
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzMGkOJi3ApCWYNDRQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Values = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Values", Int64.Type}}), Cumulative_new = List.Accumulate(#"Changed Type"[Values],{0}, (sum,index) => if index = 1 then {index} else sum& {List.Last(sum) + index}), #"Converted to Table" = Table.FromList(Cumulative_new, Splitter.SplitByNothing(), null, null, ExtraValues.Error), Cumulative_new_new = List.Accumulate(#"Changed Type"[Values], {0}, (sum,index) => sum&{ index}), //I merge two columns into a table Add_columns= Table.FromColumns(Table.ToColumns( #"Changed Type")&{Cumulative_new}) in Add_columns |

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:

1 2 3 4 5 6 7 8 9 10 11 12 |
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElPTFIRxxsuEgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), New_app = List.Accumulate(#"Added Index"[Index],{0}, (state,current) => if current = 0 then {1} else if #"Added Index"{current-1}[Category] = #"Added Index"{current}[Category] then state & {List.Last(state)} else state & {List.Last(state)+1}), Add_columns= Table.FromColumns(Table.ToColumns(#"Added Index")&{New_app}), #"Removed Columns1" = Table.RemoveColumns(Add_columns,{"Column2"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Column1", "Product"}, {"Column3", "Index"}}) in #"Renamed Columns" |

The line worth mentioning is:

1 |
New_app = List.Accumulate(#"Added Index"[Index],{0}, (state,current) => if current = 0 then {1} else if #"Added Index"{current-1}[Category] = #"Added Index"{current}[Category] then state & {List.Last(state)} else state & {List.Last(state)+1}), |

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

1 |
#"Added Index"{current-1}[Category] = #"Added Index"{current}[Category] |

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:

1 |
state & {List.Last(state)} |

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

1 |
state & {List.Last(state)+1}), |

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!

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

….

Hey,

I do not fully understand the case. Could you rephrase that again? Thanks!

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…..

I see. This might be harder than I thought. I’ll try to see what I can do this week. Thanks