Please note javascript is required for full website functionality.
MVP

Blog

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!

Newsletter