# Power Query: Seedless Accommodating Accumulating

21 March 2018

*Welcome to our Power Query blog. This week, I look at simple examples of how to use the M function List.Accumulate. As the title suggests, the seed in these examples is usually 0. Next week I will look at more examples, including a case involving records.*

The **List.Accumulate** function can transform data by performing several steps at once. This certainly sounds useful, although the description in the Microsoft help pages sounds a little intimidating…

“…Accumulates a result from the list. Starting from the initial value seed, this function applies the **accumulator** function and returns the final result…”.

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

where

**list** = the list to check

** seed **= the initial value seed

** accumulator **= the value accumulator function

The purpose of this function is to take a list, look at each item in the list and then do something with those items and sow a seed. What I need are examples of how to use this function! This week, I will look at examples where the ‘seed’ is usually set to zero, namely summing (where ‘seed’ *must* be zero) and counting. Next time, I’ll move onto examples where the ‘seed’ is not zero.

**Summing**

Before we begin – yes, I know there is a **List.Sum** function! In fact, that very function is used in Power Query: One Route to a Running Total. However, this is another way to sum the items in a list, and it will help to explain how **List.Accumulate** works. I start with a simple list:

I am going to create a new blank query from the ‘Other Sources’ option of ‘New Query’ in the ‘Get and Transform’ section on the ‘Data’ tab. I am simply going to sum the items in this list:

That’s just to prove this is the same as **List.Sum** (the purpose-built function)!

The syntax for the **List.Accumulate** sum is

**= List.Accumulate(Simple_List, 0,(state, current) => state + current)**

which essentially loops through my list looking at my ‘**current**’ value and the ‘**state**’ (in this case the value of the total so far):

**Current State State + Current**

10 0 10

20 10 30

30 30 60

40 60 100

50 100 150

60 150 210

70 210 280

80 280 360

90 360 450

100 450 550

The **accumulator**, or **value accumulator** function is an example of what is known as a ** lambda function**. Lambda is a bit like algebra for programming and I won’t attempt to explain it here! The examples used in this blog involve simple lambda functions. The ‘

**seed**’ is the starting value for the ‘

**state**’ – which here is 0 so that the sum adds up correctly. If I used ‘

**seed**’ 1, then the final amount would be 551 instead.

**Counting**

The simplest count is to count the number of items in a list. I can use my previous list for this. I create a new blank query for the **List.Accumulate** function.

I can then compare the results to **List.Count**:

This time the function is simple:

**= List.Accumulate(Simple_List2, 0,(state,current)=>state+1)**

**Current State State+1**

10 0 1

100 1 2

20 2 3

50 3 4

100 4 5

30 5 6

40 6 7

Changing the ‘**seed**’ would offset the count by the value in the seed, so I would keep it as zero. However, I could tweak my function to count how many items were 100 *(say)* quite easily:

**= List.Accumulate(Simple_List2, 0,(state,current)=>if current=100 then state+1 else state)**

I could also use similar functionality on a list of text items. Below, I want to count the number of items that have the word ‘tent’ in them:

I use **List.Accumulate** to achieve this:

The functionality I have used is:

**= List.Accumulate(Simple_List4,0,(state,current)=>if Text.Contains(current, Text.Lower("tent")) then state +1 else state)**

In other words, count if ‘tent’ is found in the text. Lowercase is not strictly necessary in my example, but it’s a good idea to always convert to lowercase when making text comparisons.

This example shows how easy it is to adapt the **List.Accumulate** function to achieve a specific result. Next time, I’ll be looking at more ways to use **List.Accumulate**.

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!