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:

  1. We can pass a seed value of any allowed in Power Query data type to function. What we can pass as a seed: table, list, number, date, record, null, duration… anything.
  2. Actually (and here I’d like to correct Gil) function’s output type is independent from the seed type.
  3. Function output type depends only from data type returned by accumulator subfunction. Real output type could be any:
    List.Accumulate({1,2,3},0,(state, current)=>(if state = 0 then "" else Text.From(state))&Text.From(current))
    // equals “123”
  4. 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).
  5. The list elements could be used in accumulator sub-function. Could be not. You can use the list only as iteration counter – accumulator will perform its manipulations as many times as the count of list elements. This means that you can use List.Accumulate as For Each … Next” loop (which has as many steps as a count of elements in list).

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:

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

  1. Here we got two lists of the same size, MyList1 and MyList2
  2. 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.
  3. 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.
  4. We can describe the accumulator function as follows:
    1. For Each current element In MyList1
    2. 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)
    3. Add the result to the state[Sum] (where state[Sum] is the previous/seed value of Sum)
    4. Increase previous/seed value of Index by 1
    5. Next
  5. 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:

(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)

Follow me: Facebooktwitterlinkedinmail

BIN2DEC in Power Query: Implementations of List.Accumulate. Part 1

After @datachant have posted on using List.Accumulate in Power Query, I was intrigued of practical implementations of this function.

As you can see from his post, List.Accumulate takes a list as an input parameter, and then, starting from initial seed value, invokes the same accumulator function on each element of list, consequently.

You just need to remember than List.Accumulate can use two parameters in accumulator function, state and current: state (i.e. current result of previous calculations) of accumulator  and a current element from given list. Final result will be a value that we got on the last iteration of accumulator function. Please note that seed value actually used as the first state value in accumulator.

The power of List.Accumulate is in iterations: we will execute accumulator function as many times as the number of elements in list. So, if we have an algorithm that needed repeating calculations on list members or as much times as list members count, List.Accumulate is the answer.

I found a small algorithm that shows a practical use and core concepts of List.Accumulate: conversion from binary number to decimal. In this algorithm bits of the binary number are used one by one, starting with the most significant (leftmost) bit. Beginning with the value 0, you need to repeatedly double the prior value and add the next bit to produce the next value.

So, if we would like to convert binary 11012 to decimal number 1310, we need to split binary number to the list of bits:

{1,1,0,1}

Then we take seed value of 0 and perform next operations: double the prior value and add the next bit, repeat while we have bits:

(seed, or first state) *2 + 1(current bit) = 1
(state) * 2 + 1(current bit) = 3
3(state) * 2 + 0(current bit) = 6
6(state) * 2 + 1(current bit) = 13

In this algorithm we can see all parameters for List.Accumulate: given list of bits from binary code, initial seed value of 0, state of accumulation transferred from the one step to the next step, and current element of a list (current bit).

To implement this algorithm in Power Query List.Accumulate we just need to get a list from given input, for example it is a text string: “101010”

BitList = Text.ToList("101010")
// here we’ll get a list of text symbols, 
// so at the next step we need to implement type transformation 
// on list elements to be able to do calculations
Bin2Dec = List.Accumulate(BitList, 0, (state, current)=> state*2 + Number.From(current))

The result could be nothing than 42, it’s quite universal 🙂

I hope that this example will help you to find more practical implementations of List.Accumulate. And if you remember that initial list could be used just as iterations counter, and a seed value could be any… to be continued.

Follow me: Facebooktwitterlinkedinmail