Teil 3: "For-Next"-Schleifen programmieren mit List.Accumulate() in M für Power Query

Hallo und willkommen zum dritten Teil der Serie "Schleifen programmieren in M". Im den ersten beiden Teilen der Serie haben wir uns mit der grundsätzlichen Möglichkeit beschäftigt, Schleifenkonstrukte in M mit Hilfe rekursiver Funktionen bzw. der Funktion List.Generate() zu erzeugen. 

Du kennst die beiden ersten Teile der Serie noch nicht? Dann lerne sie hier kennen:

Im letzten Teil der Serie möchte ich Dir eine Funktion näher bringen, mit der Du in der Lage bist, den "For-Next"-Schleifentyp in M zu erzeugen. Die Funktion List.Accumulate(). 

Was ist eine "For-Next"-Schleife? Eine "For-Next"-Schleife ist eine Schleifen-Anweisung, mit der eine darin enthaltene Anweisung eine festgelegte Anzahl von Malen wiederholt bzw. ausgeführt wird. Die Anzahl der Ausführungen ist also vor dem Start der Schleife bekannt bzw. berechenbar. In Visual Basic sieht die Grundstruktur einer "For-Next"-Schleife z.B. wie folgt aus:

[Anweisung] wird im obigen Schema also fünf mal ausgeführt, weil die Schleife solange ausgeführt wird, bis i den Wert 5 erreicht. 

Zwar versteht List.Accumulate() die Schlüsselwörter "For" bzw. "Next" nicht und kann auch keinen "counter" i aufnehmen, trotzdem kannst Du ein ähnliches Verhalten mit List.Accumulate() erzeugen. 

Schauen wir uns die grundlegende Syntax der Funktion dazu etwas genauer an:

Die Funktion List.Accumulate() ist ebenfalls vom Typ "any". Somit kann sie als Endergebnis neben Zahlen auch beliebige komplexere Values wie Tables, Lists oder Records zurückgeben.

Vergegenwärtigen wir uns diese Funktionsweise anhand eines einfachen Beispiels:

Im folgenden Beispiel wird, ausgehend vom Startwert 0 ("seed": brauner Teil), mit Hilfe von List.Accumulate() eine Addition durchgeführt. Dem ersten Parameter "list" wird eine Liste übergeben, die die Zahlen von 1 bis 10 enthält ("list": grüner Teil). In der Funktionsdefinition des dritten Parameters ("accumulator": lila) werden die Parameter "state" und "current" verwendet, um verschiedene Werte zu speichern.

Im ersten Schleifendurchlauf wird auf das erste Item in der Liste {1..10} (1. Item = 1) verwiesen. Da der Startwert mit 0 angegeben ist, wird dieser an den Parameter "state" übergeben. In "current" wird das aktuelle Item der Liste gespeichert, also der Wert 1. Innerhalb der Funktion werden "state" und "current" dann addiert und das Zwischenergebnis 1 ermittelt (0 + 1 = 1). 

Im zweiten Durchlauf wird nun auf das zweite Item der Liste verwiesen, also den Wert 2. Dieser wird an den Parameter "current" übergeben, während "state" das Zwischenergebnis des letzten Durchlaufs, also 1, beinhaltet. Entsprechend ist das Ergebnis des zweiten Durchlaufs 3 (1 + 2 = 3). 

Da die Liste "{1..10}" zehn Items beinhaltet, wird dieser Vorgang insgesamt zehn mal wiederholt. Das Endergebnis ist 55.

List.Accumulate() ist eine äußerst flexible Funktion. In der Liste im Parameter "list" kannst Du Items jeden Typs speichern. Zumeist sind dies Zahlen, es können aber ebenso Texte, Datumsangaben, Tables, Lists oder Records sein. Wie das folgende Beispiel zeigt, kann es sich auch um gemischte Listen handeln: 

Die Liste im Beispiel (grüner Teil) enthält einen Text "A", ein Record und eine Liste mit den Zahlen von 1 bis 8. Der Sinn solcher Items ist dabei natürlich stark daran gebunden, was in der Funktion im dritten Parameter mit diesen Items gemacht werden soll.

Ausgehend vom Startwert 0 wird im Beispiel bei jedem Schleifendurchlauf 1 addiert. Am Ergebnis 3 kannst Du erkennen, dass die Schleife dreimal durchlaufen wurde, weil die Liste drei Items enthält. Die Art der einzelnen Items hat also keinen Einfluss auf die Anzahl der Schleifendurchläufe.

Am obigen Beispiel kannst Du ebenfalls erkennen, dass Du die Parameter "state" und "current" nicht zwingend im Ausdruck der Funktion verwenden musst. Die obige Definition kommt ohne die Verwendung von "current", also dem Listen-Item des aktuellen Schleifendurchlaufs, aus. Ein Verzicht auf "state" hätte aber zur Folge, dass die durchgeführte Operation nicht auf dem Zwischenergebnis des vorherigen Schleifendurchlaufs aufsetzen könnte.

Neben den Values in "state" und "current" kannst Du zusätzlich auch auf Values zugreifen, die außerhalb von List.Accumulate() existieren, die also z.B. aus Parametern äußerer Funktionen oder auch aus Variablen stammen:

Möglich ist dies, weil auch für List.Accumulate() die Aussagen zum "Environment" und zu "Closures" gelten, die ich bereits in den beiden ersten Teilen dieser Serie zu rekursiven Funktionen bzw. List.Generate() erläutert habe. So kann im "accumulator" (lilafarbener Teil) der Schleife "Loop" auch auf den Parameter "weekday" der äußeren Funktion (grüner Teil) zugegriffen werden. Dadurch ist es in diesem Beispiel möglich die Liste aus der äußeren Variable "ListofDates", die alle Datumsangaben vom 01.01.2010 bis zum 17.06.2018 enthält, innerhalb von List.Accumulate() daraufhin zu untersuchen, wie oft ein bestimmter "weekday" in diesem Zeitraum vorkommt. Der "Samstag" z.B. 442 mal.

Flexibel bist Du allerdings bei der Benennung der Parameter. Anstatt "state" und "current" kannst Du alternativ auch Bezeichnungen nach Deinen Vorstellungen wählen.

Im Bezug auf die Performance gelten die gleichen Aussagen wie im zweiten Teil der Serie zu List.Generate(). Auch die Funktion List.Accumulate() unterstützt "tail call elimination". Wie der obige Funktionsaufruf zeigt, sind somit Prozesse mit zig-Millionen Durchläufen möglich.

In der benutzerdefinierten Spalte "AfA-kum." soll nun der kumulierte Abschreibungsbetrag der jeweiligen Jahre berechnet werden. Diese Berechnung kannst Du mit der folgenden Funktionsdefinition erzeugen:

Als Parameter "list" wird List.Accumulate() im grünen Teil der Definition eine Liste übergeben, die mit Hilfe der Funktion List.Range() erzeugt wird. Über List.Range() wird ein Ausschnitt aus einer Liste, hier die Spalte "AfA" der Tabelle "AFA_INDEX" ("AFA_INDEX[AfA]"), ausgegeben. Der zweite Parameter "0" von List.Range() gibt dabei an, dass der Listen-Ausschnitt ab dem ersten Eintrag der Spalte "AfA" begonnen werden soll, während der dritte Parameter über die Spalte "[Index]" festlegt, wieviele Zeilen die Liste lang sein soll. Daraus ergibt sich für die dritte Zeile der Tabelle also ein Ausschnitt mit den Zahlen -30.000, -21.000 und -14.700, weil der Ausschnitt bis [Index]=3 reicht.

Eine solche Liste mit veränderter Länge wird für jede Zeile der Tabelle erzeugt und dann innerhalb von List.Accumulate() durchlaufen. Ausgehend vom Startwert "seed" = 0 (braun unterlegt) werden im letzten Parameter die Beträge in der Liste (für Zeile drei der Tabelle also -30.000,-21.000 und -14.700) über die Funktionsparameter "state" und "current" aufaddiert und so die kumulierte Abschreibung berechnet.

Neben diesem häufig gezeigten Beispiel für die Verwendung von List.Accumulate() finden sich im Netz noch eine Reihe weitere Anwendungen. Maxim Zelensky zeigt z.B. in diesem Beitrag eine Möglichkeit, die aus Excel bekannte Funktion SUMMENPRODUKT() in Power Query unter Verwendung von List.Accumulate() nachzustellen.

Wie oben bereits angedeutet, muss sich die Verwendung von List.Accumulate() nicht zwangsläufig auf die Verarbeitung von Zahlen beschränken. Grundsätzlich können alle Arten von Values verarbeitet werden. Hierbei solltest Du aber immer prüfen, ob M nicht auch eine andere Lösungsmöglichkeit für Deine Problemstellung bereithält. Wie in den vorangegangen Teilen dieser Serie bereits besprochen, sollten Lösungen mit Hilfe von Iterationen in M eher zu den letzten Alternativen zählen, die Du in Betracht ziehst.

Mit List.Accumulate() haben wir eine Möglichkeit kennengelernt, in M "For-Next"-Schleifen zu erzeugen. Dies geschieht über drei Parameter. Dem Parameter "list", der über die Anzahl der Items in einer Liste die Anzahl der Schleifendurchläufe festlegt, die durchgeführt werden. Den Parameter "seed", der den Startwert der Schleife festlegt und den Parameter "accumulator", in dem mit Hilfe einer Funktion die Operation definiert wird, die in jedem Schleifendruchlauf durchgeführt werden soll. Dabei sind flexible Verarbeitungsprozesse mit List.Accumulate() denkbar, weil zur Verarbeitung neben Zahlen auch Tabellen, Listen oder Records in Betracht kommen.

Schon in den vorangegangenen Teilen der Serie haben wir mit rekursiven Funktionen und List.Generate() zwei Möglichkeiten kennengelernt, in M Schleifen-Konstrukte zu programmieren. 

Wir haben uns mit dem Für und Wider, den Stärken und Schwächen dieser Möglichkeiten beschäftigt und mit dem "Environment" und sog. "Closures" zwei Konzepte behandelt, die Schleifen in M noch mehr Flexibilität verleihen.

Ich hoffe die Artikelserie hat Dir gefallen und Du bist auch bei meinen nächsten Beiträgen dabei. Ich würde mich freuen, wenn Du den Artikel einfach mit anderen teilst! Vielen Dank dafür!

Bis dahin!

Viele Grüße aus Hamburg

Uwe