List.Accumulate()

Power Query's M Language's Equivalent to JavaScript's Reduce

A Quick Intro...

If you're not familiar with the Microsoft Power Platforms, Microsoft Power has two incredibly rich, robust, and dynamic languages that, when working in tandem, can create some of the most sophisticated data processing tools available to the average end-user. These languages are M (Power Query) and DAX (Power Pivot, Power BI, etc.)

I wanted to start this first post with a really powerful function in M called the:

function List.Accumulate()

The reason I wanted to showcase this M function first is because it behaves exactly like JavaScript's array.Reduce() method. It's been my experience that there are a lot of people whom have dedicated their time to master languages like M and DAX and are deemed 'not real coders.' It's my goal to get that one Excel guru who doesn't think he knows enough to learn to program to see it's really just about syntax.

List.Accumulate()

I think the easiest way to understand List.Accumulate is to break it down from its technical definition, whish is;

Accumulates a summary value from the items in the list, using accumulator. An optional seed parameter, seed, may be set. -Microsoft Docs

Or, even better, the Syntax description:

List.Accumulate(list as list, seed as any, accumulator as function) as any

Yes, while each of these are true, what in the world does it say?

Here's the way it's easiest for me to remember it:

• 'list as list' - A list in M is the equivalent to an array in JavaScript and is created by wrapping a list of items within { }
• 'seed as any' - This is your starting point (I'll explain this part later)
• 'accumulator as function' - This is a function that takes two parameters and executes the code within the function
• And finally reduces that list down to one value

Below, I've written out a very basic List.Accumulate function in Power Query;

The List = {10, 20, 30, 40, 50}
Seed (or starter) = 0
Accumulator Function = (state, current) two parameters => state + current (add the two parameters together)

Array.Reduce()

The Reduce method in JavaScript is a perfect way to step through each individual step of what's happening with List.Accumulate.

In the snippet below, a is a variable with an array assigned to it (same numbers as the previous example).

let a = {10, 20, 30, 40, 50}

Next is a function called totalA that also takes two parameters, state and current. Only now, I've console.logged both state and current so we can see what happens through each iteration of the function.

function totalA(state, current) {
    console.log(`State is equal to ${state}`);
    console.log(`Current is equal to ${current}`);
    console.log(`------------`);
    return state + current;
}

Lastly, a new variable c to hold the reduce method. Here, reduce is taking two arguments: the callback function totalA and a second, optional, argument of 0.

let c = a.reduce(totalA, 0)

console.log(c)

Here is why that optional second argument in the reduce method makes a difference: it changes the starting point of your function. With the 0, that 0 is passed as the first value into state and current is passed the first value of the array (10). Without the 0, the 10 is passed as the value of state and 20 as the value of current. (Worth noting, the starting value is not optional in List.Accumulate)

But remember, the callback function is returning the state + current through every itteration, just like with the List.Accumulate.

I hope some of you found this helpful. Hope everyone is having a great weekend!