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 grundlegende Funktionsweise von 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:

List.Generate(), Power Query, Power BI, M-Language, Schleifen in M, Do-Loop Schleife in M, Excel

Parameter 1

"initial"

Der Parameter "initial" ist eine Funktion, die den Startwert der Schleife vorgibt. Dieser Startwert kann neben einem Skalar, also einer Zahl, auch ein "Structured Value", also ein "Table", "Record" oder eine "List" sein.


Parameter 2

"condition"

Der Parameter "condition" ist eine Funktion, die List.Generate() zu einer Art "Do-Loop"-Schleife werden lässt. In der Funktionsdefinition kannst Du die Ausführungsbedingung der Schleife angeben. Solange die Bedingung erfüllt ist, werden die Schleifendurchläufe fortgesetzt. 


Parameter 3

"next"

Der Parameter "next" ist eine Funktion, in der definiert ist, welche Operation bei jedem neuen Schleifendurchlauf durchgeführt wird. Dabei wird der Startwert aus "initial" bzw. das Zwischenergebnis des letzten Schleifendurchlaufs in jedem Schleifendurchlauf verändert. Dies geschieht solange die Bedingung "condition" erfüllt ist.


Parameter 4

"selector"

Der Parameter "selector" ist der einzige der vier Parameter von List.Generate(), der optional ist, also nicht zwingend angegeben werden muss. In ihm kannst Du die Struktur festlegen, in der das Endergebnis ausgegeben werden soll. Verarbeitest Du z.B. in List.Generate() ein "Record" mit mehreren Spalten, kannst Du in "selector" angeben, welche dieser Spalten im Endergebnis ausgegeben werden sollen. Gibst Du "selector" nicht an, wird die komplette Struktur ausgegeben.


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.

List.Generate(), Power Query, Power BI, M-Language, Schleifen in M, Do-Loop Schleife in M, Excel

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.

List.Generate(), Power Query, Power BI, M-Language, Schleifen in M, Do-Loop Schleife in M, Excel

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.

Closures, "each" und das Environment-Konzept

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.

List.Generate(), Power Query, Power BI, M-Language, Schleifen in M, Do-Loop Schleife in M, Excel

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! 

List.Generate(), Power Query, Power BI, M-Language, Schleifen in M, Do-Loop Schleife in M, Excel

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:

List.Generate(), Power Query, Power BI, M-Language, Schleifen in M, Do-Loop Schleife in M, Excel

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:

List.Generate(), Power Query, Power BI, M-Language, Schleifen in M, Do-Loop Schleife in M, Excel

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.

List.Generate(), Power Query, Power BI, M-Language, Schleifen in M, Do-Loop Schleife in M, Excel

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.

Wofür kannst Du List.Generate() noch nutzen?

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!

List.Generate() und das Thema Performance

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.

List.Generate(), Power Query, Power BI, M-Language, Schleifen in M, Do-Loop Schleife in M, Excel

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.

Was haben wir gelernt und wie geht es weiter?

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:

Kennst Du schon die anderen Teile der Serie?

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

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