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!
