Teil 2: "Do-Loop"-Schleifen programmieren mit List-Generate() in M für Power Query

Im ersten Teil der Serie haben wir uns grundsätzlich mit der Möglichkeit befasst Schleifenkonstrukte, ähnlich denen anderer Programmiersprachen, zu programmieren. Wir haben uns hierzu das Konzept der rekursiven Funktion in M angeschaut, haben uns damit beschäftigt wie Rekursionen funktionieren, warum sie funktionieren, aber auch, welche Nachteile mit ihrer Verwendung einhergehen. 

Du kennst den ersten Teile der Serie noch nicht? Dann lerne ihn hier kennen:

In diesem Teil möchte ich Dir nun einen alternativen Weg zeigen, mit dem Du Schleifen in Power Query erzeugen kannst und gleichzeitig die Nachteile herkömmlicher rekursiver Funktionen im Hinblick auf die Performance überwindest. Die Funktion List.Generate().

Die Frage, was diese Funktion eigentlich tut, ist schnell beantwortet:

"List.Generate() generiert eine Liste von Werten auf der Grundlage von vier Funktionen"

Dafür erwartet Sie die Übergabe von bis zu vier Parametern. All diese Parameter sind ihrerseits wieder Funktionen. Werfen wir zunächst einen Blick auf die Funktionsdefinition und die einzelnen Parameter:

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

Im folgenden Beispiel wird mit Hilfe von List.Generate() eine Liste von Zahlen erzeugt. Als erster Parameter wird in "initial" eine Funktion übergeben, die den Startwert der Schleife angibt. Der Startwert soll in unserem Beispiel 10 sein. Weil List.Generate() nur Funktionen als Parameter akzeptiert, wird diesem Startwert die Syntax einer Funktionsdefinition ("()=>") vorangestellt.

Im zweiten Parameter "condition" findet sich eine Funktion, die die Bedingung festlegt, unter der die Schleife fortgesetzt wird. Im Beispiel soll die Schleife fortgesetzt werden, solange das Ergebnis des vorangegangenen Schleifendurchlaufs größer oder gleich -5 ist. Im dritten Abschnitt wird der Parameter "next" übergeben. Dieser enthält die Operation, die in jedem Schleifendurchlauf durchgeführt werden soll. In unserem Fall soll der Ergebniswert jedes Durchlaufs um 2 verringert werden. Die Funktion erzeugt also in acht Durchläufen eine Liste mit den Zahlen von 10 bis -4. Den optionalen vierten Parameter "selector" benötigen wir an dieser Stelle nicht.

Verändern wir nun einmal die Ausführungsbedingung "condition":

Im unten stehenden Beispiel setzen wir die Bedingung auf ">=10" anstelle von ">=-5". Ein Blick auf die ausgegebene Liste zeigt, dass lediglich der Startwert "10" ausgegeben wird, weil für diesen die Bedingung noch erfüllt ist.

Alle weiteren Schritte erfüllen die Bedingung nicht und werden entsprechend nicht gelistet, weil kein weiterer Schleifendurchlauf stattfindet. Es zeigt sich also, dass es sich bei List.Generate() quasi um eine kopfgesteuerte Schleife handelt. Im Gegensatz zu sog. fußgesteuerten Schleifen zeichnet sich diese dadurch aus, dass ihre Ausführungsbedingung vor dem ersten Schleifendurchlauf geprüft wird. Während fußgesteuerte Schleifen mindestens einen Durchlauf durchführen, brechen kopfgesteuerte Schleifen ab, wenn die Ausführungsbedingung gleich zu Beginn nicht erfüllt ist. List.Generate() ist also mit einer "Do-While-Loop"-Schleife vergleichbar.

Der erste Parameter von List.Generate() wird durch die übliche Funktionsdefinition in M, gefolgt vom Startwert der Schleife, eingeleitet (im letzten Beispiel "()=> 10"). Dir fällt eventuell auf, dass die Funktion keine Parameter aufweist. Der Grund hierfür ist, dass die Funktion innerhalb einer anderen Funktion, in diesem Fall List.Generate(), ausgeführt wird.

Würde sie einen Parameter-Input erwarten, wie im Bild oben, würde dieser zu einem Fehler führen, weil Du keine Möglichkeit hast dieser Funktion den geforderten Parameter zu übergeben, solange sie innerhalb von List.Generate() ausgeführt wird. Die Nutzung eines optionalen Parameters, z.B. (optional Startwert)=>, würde zwar keinen Fehler erzeugen, hätte aber keinen Mehrwert, weil der Parameter wie gesagt nie genutzt werden könnte.

Bedeutet das nun, dass Du den Startwert der Schleife immer als festen Wert im ersten Parameter von List.Generate() hinterlegen musst? Nein, denn an dieser Stelle kommt dir das Environment-Konzept von M zur Hilfe! 

Ausdrücke im Body einer Funktion können in M auf alle Variablen zugreifen, die sich bei der Initialisierung der Funktion im Environment, quasi im Scope der Funktion befinden. So hast Du auch ohne die Nutzung von Parametern die Möglichkeit den Startwert in einer gesonderten Variable oder sogar in einer eigenen Abfrage festzulegen, sofern diese sich, wie im obigen Beispiel, im Environment der Variable "CreateList" bzw. im globalen Environment befindet und "CreateList" so auf die Werte zugreifen kann.

Nutzt Du, wie im folgenden Beispiel, List.Generate() innerhalb einer anderen Funktion, kannst Du zusätzlich auf das Konstrukt der sog. "Closures" vertrauen:

Closures sind ein typisches Konzept aus funktionalen Sprachen wie JavaScript, F# oder eben M und werden erzeugt, wenn eine Funktion definiert wird. Sie ermöglichen einer Funktion den Zugriff auf Parameter außerhalb ihres Scopes. Die äußere Funktion bildet die "Closure" für die Innere. Vereinfacht gesagt verbinden Closures die Scopes von ineinander geschachtelten Funktionen und ermöglich diesen Funktionen auf diese Weise über alle Schachtelungsebenen auf die Parameter der jeweils äußeren Funktion zuzugreifen. Aus diesem Grund kann der erste Parameter von List.Generate() auch ohne explizite Angabe eines Parameters auf den Parameter "Startwert" zugreifen, der der äußeren Funktion übergeben wird.

Die weiteren Parameter-Funktionen von List.Generate() beginnen hingegen nicht mit "()=>", trotzdem sind es natürlich Funktionen. Sie werden mit "each" eingeleitet.

Der Ausdruck "each" ist in M eine vereinfachte Deklaration einer Funktion mit einem einzigen Parameter, nämlich dem Parameter "_". Die Verwendung von "each" ist also gleichbedeutend mit der Funktionsdeklaration "( _ )=>", wobei "_" ein rein formal gewählter Parameter ist, der ebenfalls ganz anders benannt werden könnte. Entsprechend sind die drei im folgenden Schaubild stehenden Definitionen identisch und führen alle zum gleichen Ergebnis:

Der Parameter von "each", also "_",  nimmt dabei innerhalb von List.Generate() immer den Wert der aktuellen Zeile der generierten Liste, also das Zwischenergebnis des letzten Schleifendurchlaufs, auf.

Dies zeigt sich besonders plastisch, wenn man im dritten Abschnitt im Body der Funktion den "_"-Parameter nicht verwendet.

Dann wird das Zwischenergebnis des Schleifendurchlaufs nicht im dritten Parameter weiterverarbeitet und entsprechend der Startwert 10 nicht in jedem Durchlauf um 2 reduziert, die "condition" ">=-5" also nie außer Kraft gesetzt.

List.Generate() ermöglicht Dir, wie gesagt, nicht nur die Veränderung von Zahlen als Startwert. Neben skalaren Werten kann sie ebenfalls "Structured Values" wie "Tables", "Lists" oder "Records" als Ausgangspunkt aufnehmen und dann innerhalb der Schleife verarbeiten. Diese Möglichkeit erweitert das Einsatzgebiet von List.Generate() enorm. Im Netz findest Du eine Vielzahl an Beispielen, in denen List.Generate() nutzbringend eingesetzt wird. Zwei seien an dieser Stelle stellvertretend genannt:

Chris Webb nutzt List.Generate() in diesem Beitrag, um über verschiedene Tabellen zu iterieren. In Tabelle A stehen dabei Texte, in denen Wörter ersetzt werden sollen. In Tabelle B stehen die entsprechenden Zuordnungen für die einzusetzenden Worte. In den Schleifendurchläufen wird nun die Ersetzung vorgenommen, indem ermittelt wird, welche Worte in Tabelle A durch welche Worte aus Tabelle B zu ersetzen sind. In seinem Beitrag nutzt Chris z.B. auch den optionalen vierten Parameter, um am Ende der Schleifen nur ein Feld des erzeugten Records auszugeben.

In einem anderen Beitrag auf "Excelando" werden Schleifen mit List.Generate genutzt, um den Aufruf einer Web-API zu optimieren. Beim Abruf der API müssen einzelne Seiten angesprochen werden, wobei im Vorfeld nicht bekannt ist, wieviele Seiten es gibt, auf denen Daten vorhanden sind. Die eingesetzte Schleife prüft also, ob sich auf einer Seite noch Daten befinden und bricht ab, sobald die erste Seite aufgerufen wird, die leer ist. Statt pauschal eine fixe, ausreichend hohe Zahl an Seiten abzurufen, kann der API-Call auf diese Weise auf die Anzahl an Aufrufen reduziert werden, die nötig ist, um alle Daten zu erhalten. So können eventuelle Fehlermeldungen beim Aufruf leerer Seiten vermieden und die Nutzung der API effizienter gestaltet werden.

Diese beiden Beispiele sollen lediglich verdeutlichen, wie breit das Gebiet ist, in dem List.Generate() zum Einsatz kommen kann. Auch bei der Verwendung von List.Generate() gilt: Deiner Kreativität sind kaum Grenzen gesetzt!

Auch auf die Gefahr hin mich zu wiederholen, möchte ich meine Empfehlung aus dem ersten Teil der Serie zu "Schleifen in M" an dieser Stelle erneuern: Schleifen sollten als Lösungsansatz in M erst als eine der letzten Alternativen in Betracht gezogen werden. Sie sollten erst dann ein Thema sein, wenn die Problemstellung mit den Standard-Funktionen der M-Bibliothek nicht gelöst werden kann. Die Verarbeitung ganzer Daten-Sets ist der Einzelbehandlung von Zeilen immer vorzuziehen.

An rekursiven Funktionen hatten wir im ersten Teil dieser Serie kritisiert, dass sie die sog. "tail call elimination" nicht unterstützen und deshalb bei ca. 11.000 Schleifendurchläufen zu einem Fehler und damit dem Abbruch der Schleife führen. Dieses Problem hat List.Generate() nicht. Die obige Abbildung zeigt, dass auch zig-Milliarden Scheifendurchläufe problemlos möglich sind und dies bei deutlich verbesserter Performance im Vergleich zu rekursiven Funktionen.

Ein zusätzlicher Performance-Tipp: Greifst Du innerhalb von List.Generate() auf "Tables" oder "Lists" zu, solltest Du diese mit Table.Buffer("Deine Tabelle") bzw. List.Buffer("Deine Liste") in den Arbeitsspeicher verlagern. Auf diese Weise verhinderst Du, dass die "Table" bzw. "List" bei jedem Schleifendurchlauf neu evaluiert wird und erhöhst so die Performance. Diesen Vorteil erkaufst Du Dir allerdings damit, dass dann kein "Query Folding" mehr möglich ist. Am Ende ist die Verwendung von Table.Buffer() bzw. List.Buffer() also ein Trade-Off. Da "Query Folding" allerdings lediglich für bestimmten Datenquellen, wie z.B. Datenbanken (SQL Server, Oracle etc.), relevant ist und bei anderen Quellen, wie Excel- oder Flat-Files, keine Rolle spielt, ist dies nicht immer ein Nachteil.

Mit List.Generate() haben wir eine Möglichkeit kennengelernt, "Do-While-Loop"-Schleifen in M für Power Query zu erzeugen. Sie ist eine gute Alternative zur klassischen rekursiven Funktion, weil sie deutliche Vorteile hinsichtlich der Performance aufweist und zudem "tail call elimination" unterstützt. 

List.Generate() verfügt über vier Parameter, die allesamt ihrerseits Funktionen sind. Der Parameter "initial" ist dabei der Startwert der Schleife. Beim Startwert kann es sich um skalare Werte aber auch strukturierte Werte wie "Tables", "Records" oder "Lists" handeln. Diese müssen nicht als feste Werte in List.Generate() hinterlegt sein, sondern können auch aus Variablen bzw. Parametern außerhalb von List.Generate() bezogen werden.

Der Parameter "condition" beinhaltet die Ausführungsbedingung der Schleife, während im Parameter "next" definiert wird, was in jedem Schleifendurchlauf mit dem Zwischenergebnis des vorangegangenen Durchlaufs passieren soll. Über den optionalen Parameter "selector" kann die Ausgabe des Endergebnisses beeinflusst werden.

Im dritten und letzten Teil der Serie geht es um die Erzeugung von "For-Next"-Schleifen in Power Query. Hierzu greifen wir auf die Funktion List.Accumulate() zurück. Wenn Dich die anderen Teile dieser Serie ebenfalls interessieren, erfährst Du hier mehr:

Ich hoffe der Artikel hat Dir gefallen und Du bist auch beim dritten und letzten Teil der Serie dabei. Wenn dem so ist, dann teile den Artikel doch einfach mit anderen!

Bis zum nächsten Mal!

Viele Grüße aus Hamburg

Uwe