Part 3: Programming "For-Next"-Loops using List.Accumulate() in M for Power Query

Welcome to the third and last part of the series "Programming loops in M". In the first two parts we dealt with some options to create loop constructs in M with the help of recursive functions or the function List.Generate().

You haven't seen the first two parts of the series yet? Then get to know them here:

In this part of the series I would like to introduce you to a function with which you are able to create the "For-Next"-loop type in M. The function List.Accumulate().

What is a For-Next-loop? A For-Next-loop is a loop statement that repeats or executes a statement contained in it a specified number of times. The number of executions is therefore known or calculable before the start of the loop. In Visual Basic, for example, the basic structure of a "For-Next" loop looks like this:

So [Your Code] is executed five times in the above scheme, because the loop is executed until i reaches the value 5.

Although List.Accumulate() does not understand the keywords "For" or "Next" and cannot include a "counter" i, you can still create a similar behavior using List.Accumulate().

Let's take a closer look at the basic syntax of the function:

The function List.Accumulate() is also of type "any". Thus appart from all standard value types it can also return any structured value like tables, lists or records as a final result.

Let us visualize this functionality by means of a simple example:

In the following example, an addition is performed using List.Accumulate(), starting from a value of 0 (parameter "seed": brown part). A list containing the numbers from 1 to 10 (green part) is passed to the first parameter "list". In the function definition of the third parameter ("accumulator": purple), "state" and "current" are used to store the result of the loops last pass and the value from "list" for the current iteration.

In the first loop pass, the first item in the list {1..10} (1st item = 1) is referenced. Since the start value is 0, 0 is passed to the "state" parameter. In "current" the current item of the list is stored, i.e. the value 1 for the first iteration. Within the function "state" and "current" are added and an intermediate result of 1 is determined (0 + 1 = 1).
In the second pass, the second item of the list is referenced, i.e. the value 2. This is passed to the parameter "current", while "state" contains the intermediate result of the last pass, i.e. 1. Accordingly, the result of the second pass is 3 (1 + 2 = 3).
Since the list "{1..10}" contains ten items, this process is repeated ten times. The final result is 55.

List.Accumulate() is an extremely flexible function. In the list in the first parameter you can store items of any type. In most cases these are numbers, but they can also be texts, dates, tables, lists or records. As the following example shows, they can also be mixed lists:

The list in the example (green part) contains a text "A", a record and a list with the numbers from 1 to 8. The choice of such list items is of course strongly bound to what you want to do with these items in the third parameter "accumulator".

Starting from "seed"=0, 1 is added for each loop pass in this example. You can see from result of 3 that the loop was passed three times because the list contains three items. The type of that individual items has no influence on the number of iterations.

The example above shows that you don't have to use the parameters "state" and "current" in the expression of the function in "accumulator". The definition does not use "current", the current item of "list" in the current iteration. If you would not use "state", however, the operation in "accumulator" would not be able to respect the intermediate result of the previous loop pass in the calculation.

In addition to the values in "state" and "current", you can also access values that exist outside of List.Accumulate(), e.g. values from parameters of external functions or variables:

This is possible because the statements on "Environment Concept" and "Closures" I made in the first two parts of this series about recursive functions and List.Generate() also apply to List.Accumulate(). In the "accumulator" (purple part) of "Loop" the parameter "weekday" from the outer function (green part) can also be accessed. This makes it possible to examine the list from the outer variable "ListofDates" (brown part), which contains all dates from January 1st 2010 to June 17th 2018, on how often a certain "weekday" occurs in this period using List.Accumulate(). The "Saturday" e.g. 442 times.

Additional flexibility comes in when we are talking about naming the parameters. Instead of "state" and "current" you can alternatively choose names according to your ideas.

In terms of performance, the same statements apply as in the second part of the series on List.Generate(). List.Accumulate() also supports "tail call elimination". The example above illustrates that processes with tens of millions of loops are possible.

In the user-defined column "AfA-kum.", the accumulated depreciation amount for the respective years must be calculated. You can create this calculation with the following function definition:

As list parameter (green part) we inject a list to List.Accumulate(), which is generated by the function List.Range(). List.Range() returns a section of a list, here the column "AfA" from table "AFA_INDEX" ("AFA_INDEX[AfA]"). The second parameter "0" of List.Range() specifies that the list section should start from the lists first entry in the "[AfA]" column, while the third parameter uses the "[Index]" column to specify the length of the list in terms of number of rows. This results in a list with the items -30,000, -21,000 and -14,700 for the third row of the table, because the section returned by List.Range() for this line goes up to [Index]=3.

Such a list with changing length is created for each row of the table and then iterated by List.Accumulate(). Starting from the start value "seed" = 0 (brown color), the values in the list (for line three of the table -30,000, -21,000 and -14,700) are added up using the parameters "state" and "current" in the "accumulator to calculate the accumulated depreciation.

In addition to this frequently shown example for the use of List.Accumulate(), there are a number of other applications in the web. Maxim Zelensky, for example, shows a way to recreate the SUMPRODUCT() function from Excel in Power Query using List.Accumulate(). (Please find his post here)

As indicated above, the use of List.Accumulate() does not necessarily have to be limited to processing numbers. Basically, all types of values can be processed. However, you should always check whether M offers another solution for your problem. As already discussed in the previous parts of this series, solutions with the help of iterations in M should be among the last alternatives you consider.

With List.Accumulate() we got to know a possibility to create "For-Next"-loops in M. This is done by three parameters. The parameter "list", which determines the number of loop passes that are performed by the number of items the list contains. The parameter "seed", which determines the start value of the loop, and the parameter "accumulator", in which a function is used to define the operation to be performed in each loop pass. Flexible processing with List.Accumulate() is possible, because tables, lists or records can be processed as well as numbers or texts.

Already in the previous parts of the series we got to know two possible solutions for programming of loop constructs in M, recursive functions and List.Generate().

We have dealt with the pros and cons, the strengths and weaknesses of these solutions and concepts like "Environments" or  "Closures", two concepts that give loops in M even more flexibility.

I hope you enjoyed the series and you will be there for my next contributions. I would be happy if you would share the series with others! Many thanks for reading!

Greetings from Hamburg, Germany

Uwe