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()

"For-Next"-Schleifen und die Funktionsweise von 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:

For-Next-Loop, Power Query, Power BI, M-Language, Schleifen in M, For-Next Schleife in M, Excel

[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:

List.Accumulate(), Power Query, Power BI, M-Language, Schleifen in M, For-Next Schleife in M, Excel

Parameter 1

"list"

Als ersten Parameter erwartet List.Accumulate() eine Liste. Wesentliche Funktion des Parameters "list" ist die Festlegung der Anzahl der Schleifendurchläufe. Dies geschieht über die Anzahl der in der Liste enthaltenen Items und nicht über die Items selbst.


Parameter 2

"seed"

Der Parameter "seed" ist der Startwert der Schleife bzw. der Iteration. Da er vom Typ "any" ist, kann dieser Parameter neben Zahlen beliebige andere Values, wie Tables, Lists oder Records als Startwert aufnehmen


Parameter 3

"accumulator"

Der letzte Parameter von List.Accumulate() ist eine Funktion. In dieser Funktion wird der eigentliche Ausdruck hinterlegt, der bei jedem Schleifendurchlauf ausgeführt wird. Die Funktion "accumulator" weist dabei die zwei Parameter "state" und "current" auf. In "state" wird in jeder Iteration das Zwischenergebnis aus dem vorangegangenen Schleifendurchlauf gespeichert. "Current" enthält unterdessen immer das in der aktuellen Iteration erreichte Item aus der Liste des ersten Parameters "list".


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.

List.Accumulate(), Power Query, Power BI, M-Language, Schleifen in M, For-Next Schleife in M, Excel

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.

Wie flexibel kannst Du List.Accumulate() einsetzen?

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: 

List.Accumulate(), Power Query, Power BI, M-Language, Schleifen in M, For-Next Schleife in M, Excel

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:

List.Accumulate(), Power Query, Power BI, M-Language, Schleifen in M, For-Next Schleife in M, Excel

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.

state, current, List.Accumulate(), Power Query, Power BI, M-Language, Schleifen in M, For-Next Schleife in M, Excel

Dies bedeutet allerdings nicht, dass Du die beiden Parameter in der Funktionsdefinition von "accumulator" weglassen kannst. Weil List.Accumulate() die Werte für "state" und "current" an "accumulator" übergeben möchte, wird das Fehlen dieser Parameter in der Funktionsdefinition, wie im Bild zu sehen, mit einer entsprechenden Fehlermeldung quittiert.

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

List.Accumulate(), Power Query, Power BI, M-Language, Schleifen in M, For-Next Schleife in M, Excel

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.

Wofür kannst Du List.Accumulate() nutzen?

List.Accumulate(), Power Query, Power BI, M-Language, Schleifen in M, For-Next Schleife in M, Excel
Beispiel für die Berechnung von kumulierten Abschreibungen mit List.Accumulate()

Ein naheliegendes Beispiel für den Einsatz von List.Accumulate() ist die Berechnung von laufenden Summen bzw. kumulierten Werten.

Ein klassischer Anwendungsfall aus dem Finanzbereich ist die Berechnung von Abschreibungen (AfA), also Wertverlusten von Vermögensgegenständen. In der nebenstehenden Tabelle "AFA_INDEX" ist beispielhaft eine Abschreibungsvorausschau für einen Vermögens-gegenstand für die Jahre 2017 bis 2027, mit den entsprechenden Abschreibungsbeträgen der Jahre (Spalte "AfA") und den resultierenden Restbuchwerten (Spalte "Buchwert"), abgebildet.

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:

List.Accumulate(), Power Query, Power BI, M-Language, Schleifen in M, For-Next Schleife in M, Excel

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.

Was haben wir in dieser Artikelserie gelernt?

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. 

Kennst Du schon die anderen Teile der Serie?

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

BESTENS INFORMIERT
Entwickle Dein Know-How und Deine Möglichkeiten im Reporting
und erhalte mit dem Newsletter exklusive Beispieldateien
zu unseren Artikeln

DURCHSUCHE data-insights.de...

RSS-FEED BI BLOG...


Kommentar schreiben

Kommentare: 0