# SUMPRODUCT() and For Each loops in Power Query: Implementations of List.Accumulate. Part 2

In my mind ** List.Accumulate** is one of most undocumented and most underestimated functions in Power Query ‘M’ language. In this post I would like to continue explanation of this very interesting iterative function.

## Data types

In my previous post about List.Accumulate function in Power Query ‘M’ language I’ve mentioned that ** seed** value, passed to function, could be any. As it was described in Gil Raviv post,

The seed argument (or as we call it the initial state), should be of the same type as the first argument of your accumulator function and of the same type as of the function’s output.

Now I want to add more details to an explanation of the List.Accumulate arguments’ behavior:

- We can pass a
*seed*value ofin Power Query data type to function. What we can pass as a__any allowed__*seed*: table, list, number, date, record, null, duration… anything. - Actually (and here I’d like to correct Gil)
.__function’s output type is independent from the__*seed*type - Function output type depends only from data type returned by
*accumulator*sub*–*function. Real output type could be any:

12List.Accumulate({1,2,3},0,(state, current)=>(if state = 0 then "" else Text.From(state))&Text.From(current))// equals “123” - The first (
*state*) argument in*accumulator*sub-function has to be the same type as*seed*value only at first iteration and only if*state*argument is used in*accumulator*(I really see no sense in manipulations without using*state*argument, but you can do it, that’s why I noted it). After the first iteration the*accumulator*result will totally depend on what this sub-function does (as shown above). - The
*list*elements could be used in*accumulator*sub-function. Could be not. You can use the*list*only– accumulator will perform its manipulations as many times as the count of list elements. This means that you can use__as iteration counter__*List.Accumulate*as**“**(which has as many steps as a count of elements in list).__For Each … Next” loop__

Let us try to look at how those features could be implemented.

## SUMPRODUCT () in Power Query

For a starter let’s try to convert the famous Excel function, SUMPRODUCT, to Power Query function.

**Why we really need it?**

There are a lot of ways to implement SUMPRODUCT in Power Query. For example, we can add custom column with formula like this: = [Amount] * [Price] and then just get the sum of it. It’s easy and clear, except we need to add this helper column to get just one number.

But let suppose we have got two lists by some query manipulations and these lists are not columns in any table. We can transform them to tables, and then somehow combine those tables, perhaps with Index column, and… looks not as easy as it was with columns in one table?

### SUMPRODUCT itself

With *List.Accumulate* we can do it with just one row of code:

1 2 3 4 5 6 |
let myList1 = {1,1,2,2,4,5}, myList2 = {1,4,2,2,1,5}, SUMPRODUCT = List.Accumulate(MyList1, [Sum=0, Index=0], (state, current) => [Sum = current * MyList2{state[Index]}+state[Sum], Index = state[Index]+1])[Sum] in SUMPRODUCT |

Obviously it equals 42.

**How it works**

- Here we got two lists of the same size, MyList1 and MyList2
- We’ll use one of lists as an
*list*argument of List.Accumulate. We actually will use its elements in calculation, but also the count of list elements will be used as a counter to iterations inside our function. - As the seed argument we used a record: [Sum=0, Index =0], where
*Sum*will be used as field for calculation and*Index*will be used as list’s current position counter. - We can describe the accumulator function as follows:
**For Each***current element***In**MyList1- Multiply
*current*on MyList2{state[Index]} (where state[Index] is the value of*Index*from previous step or from*seed*if it is first step) - Add the result to the state[Sum] (where state[Sum] is the previous/seed value of
*Sum*) - Increase previous/seed value of Index by 1
**Next**

- At the end of the loop we have the result as a record of two fields:
*Sum*and*Index*, as a result of*Accumulate*. We need only*Sum*field value, so we just add this filed name in square brackets at the end of row to get its value. Actually it means “give me the value of the [Sum] field from List.Accumulate result”.

### SUMPRODUCT() UDF for PowerQuery/’M’

You can easily transform code from above to the one-row function for future uses:

1 2 3 4 5 |
(MyList1 as list, MyList2 as list) as number => let SUMPRODUCT = List.Accumulate(MyList1, [Sum=0, Index=0], (state, current) => [Sum = current * MyList2{state[Index]} + state[Sum], Index = state[Index] + 1])[Sum] in SUMPRODUCT |

Of course, you can enhance it with error handling and other feautures, as you want.

Enjoy! 🙂

(to be continued)

kyle

February 4, 2020 - 22:04

Amazing post, thank you!

Would it be possible to call another function using this method? I want to insert ‘Error’ rows in an existing table at different intervals using a list. The reason is to create “breaks” so I can fill values down. I am attempting to flatten a hierarchy, but the hierarchy is very inconsistent, which prevents me from doing a straight fill operation on the elements. Annoying.

Anyway, I am trying something like this:

RowInsertionList = {5,7,9,16,18…3141} //Row number to insert error rows, this is not my full list but represents the range of row insertions required. Original table is only 1,880 rows, so I am inserting 1,261 rows at irregular intervals to add up to the 3,141 last row insertion.

So then I defined a function called “InsertRows” to insert rows in the desired table:

(InsertRows as number)=>

let

Insert=Table.InsertRows(#”Output Table”,InsertRows,{[]})

in

Insert

This function performs as intended, and inserts the Error row wherever you specify.

So next, I want to invoke that function in a loop to insert the 1,261 different rows into “Output Table” per the RowInsertionList. I am doing something like this, but I know I’m missing an element:

=List.Accumulate(RowInsertionList, 0, (state, current)=> InsertRows(current))

This just outputs the last iteration of the loop…which inserts the error row at the end. Any ideas? Thanks in advance!

sam

July 5, 2016 - 05:40

Here is a alternate way

let

Lst1= {1,1,2,2,4,5},

Lst2 = {1,4,2,2,1,5},

Ctr = {0..List.Count(Lst1)-1},

mSumProduct = List.Accumulate(Ctr,0,(state,current)=> state + Lst1{current}*Lst2{current})

in

mSumProduct

Maxim Zelensky

July 12, 2016 - 18:59

Hello Sam!

Nice code, thanks! It looks much simplier than mine 🙂

Curt Hagenlocher

June 17, 2016 - 18:59

I don’t want to take anything away from your otherwise excellent blog post, but because of the (admittedly hard-to-internalize) lazy semantics of M lists, this probably isn’t the best way to implement SUMPRODUCT. How I do it is like this:

(list1, list2) => List.Sum(Table.AddColumn(Table.FromColumns({list1, list2}), “Product”, each [Column1] * [Column2])[Product])

One of the hardest things about using M is that the idioms of imperative languages — even if available — often have negative consequences. Set operations are nearly always preferable to operations on individual values. Indexing operations in particular can have surprising results with respect to performance.

Maxim Zelensky

July 12, 2016 - 18:54

Hello Curt!

Sorry for late answer and thank you for the comment and really important notes. I like your trick with adding column “on-the-fly” – it could really help in many cases.

I just want to mention that I took Sumproduct() just for example of practical use, because it is popular and not implemented in ‘M’ yet (I do not sure whether it would be implemented).

Actually we can make more complex calculations with List.Accumulate. For example, if we would like to make different transformations to list elements depending on their positions or values. The simpliest example is where list element’s position is denominator or multiplicator.

Of course, ‘M’ and Power Query not designed for this purposes (in common), but we can, thats why 🙂