Please note javascript is required for full website functionality.

Blog

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&current)

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&current else state&" : "&current)

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!

Newsletter