# Power Query: Seeded Accommodating Accumulating

28 March 2018

*Welcome to our Power Query blog. This week, I look at the M function List.Accumulate and its uses where the seed is not zero.*

As I described last week, the **List.Accumulate** function can transform data by performing several steps at once, and the description is below:

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

Last week, in Seedless Accommodating Accumulating, I looked at some examples where the ‘seed’ is usually set to zero (summing and counting). This time, I am looking at examples where the ‘seed’ is not zero. I am going to provide some examples as a demonstration here: maximum, multiplication, concatenation and record conditions.

*Maximum*** **

There is a **List.Max** function in **M** language which could be used to do achieve this goal, so I will be comparing the results from this with my **List.Accumulate** calculation. I begin with another list, just for a change:

I create a new blank query which uses **List.Accumulate**.

And, just to show that **List.Max** gives me the same value:

Going back to my **List.Accumulate** syntax, I have:

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

This time, **List.Accumulate** goes through each item in my list and updates ‘**state**’ if the ‘**current**’ value is greater than the greatest so far

**Current State New Value for State**

10 0 10

100 10 100

20 100 100

50 100 100

100 100 100

30 100 100

40 100 100

In this case, I have set the ‘seed’ to zero (0), but it just needs to be less than or equal to the maximum for the function to work. For minima, it would be necessary to set the ‘seed’ to a value higher or equal to the minimum (so this is not a seedless example!).

*Multiplication (or Division)*

When using **List.Accumulate** to perform calculations involving multiplication or division, the ‘seed’ is important. It can’t be zero because it will either render the product zero or invalidate the division. Allow me to illustrate with another list.

This time my blank query will look at the product of the items on my list.

This time, my function is

**= List.Accumulate(Simple_List3,1,(state,current)=>state*current)**

which acts on my list as follows:

**Current State State*current**

10 1 10

2 10 20

100 20 2000

In this case, ‘**seed**’ is a factor in my calculation. If I change it to zero, then my result is zero, because the first value for the state will be zero.

If I change ‘**seed**’ to 2, the first value of ‘**state**’ will be 2, and the whole calculation will be multiplied by 2.

The division is similar:

where **List.Accumulate** acts on my list accordingly:

**Current State State/current**

10 1 0.1

2 0.1 0.05

100 0.05 0.0005

Clearly, the seed can’t be nought as my first step would result in an error caused by dividing by zero.

*Concatenating*

I will use my text list ‘**Simple_List4**’ for this, which was a list of tent equipment. I can concatenate all my items using **List.Accumulate**:

This has extracted all the items in my list and put them into a single string.

**= List.Accumulate(Simple_List4," ", (state,current)=>state¤t)**

I could add a delimiter to make it clearer. The delimiter would only be needed after the first item, so I would need to check if it was the first item, and if not, add my delimiter.

**= List.Accumulate(Simple_List4," ", (state,current)=> if state = " " then state¤t else state&" : "¤t)**

The results are shown below:

*Conditions on Records*** **

The examples so far show how **List.Accumulate** works, but one area where it can be very useful, is when I have a list of records. I have an example below where my list is made up of records:

Each record has some item data, but I want to know the position of records that mention a ‘**Tent**’. This would take several steps, as I can’t see the contents of the records in the list, but I can use **List.Accumulate**.

I haven’t tried to stop the comma delimiter appearing before the first record found as I want to keep the syntax as simple as it can be.

**= List.Accumulate(List_Records,"",(state,current)=>if Record.HasFields(current,"Tent") then state&" , "&Text.From(List.PositionOf(List_Records,current)) else state)**

This looks at each item on the list, and checks for the field ‘**Tent**’. If it is found then the function returns the position number of the record (which starts at 0, not 1). As the function loops round each item, the delimiter is placed between each result.

**Current state new value for state**

Record (1) “ “ ,0

Record (2) ,0 ,0

Record (3) ,0 ,0,2

which tells me that records 1 and 3 have the field ‘**Tent**’ in them.

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!