Von Lukas Hillesheim, 30. August 2022

Blog Article & Copyright by Lukas Hillesheim

In Teil 1 dieser Serie wird gezeigt, wie Lambda Expressions innerhalb von Power Query Iteratoren eingesetzt werden. Von den vielen Power Query Funktionen gehören einige zur Klasse der Iteratoren. Dabei werden Lambda Expressions, die sich im Inneren eines Iterators befinden, wiederholt ausgeführt. Bei jedem Aufruf übergibt der Iterator andere Daten an die Lambda Expression.

Inhalt:

1. Iteratoren in Power Query
2. Lambda Expressions - Beispiel 'Table.SplitColumn'
3. Zusammenwirken von Iteratoren und Lambda Expressions
4. Alternative Syntax
5. Benutzerdefinierte Funktionen als Lambda Expressions

Nicht nur in Power Query, sondern auch in SQL, Python, DAX etc. gibt es ein grosses Set an Funktionen, von denen viele die Logik von Schleifen verkapseln. Häufig kann man vom Namen der jeweiligen Funktion auf die Existenz einer Schleife schliessen, manchmal jedoch nicht. Und in einigen Fällen ist es sogar schwer, die jeweilige Funktion als Iterator zu verstehen.

1. Iteratoren in Power Query

1.1. Funktions-Namen mit offensichtlichen Bezug zu Iterationen

Der Name der Funktion List.Accumulate macht deutlich, dass diese Funktion eine Liste als Eingabe erwartet und dann auf Basis der Liste ein einzelnes Element als Ergebnis zurückgibt. Es ist offensichtlich, dass zur Herleitung des Ergebnisses eine interne Schleife ausgeführt wird:

// Input: 
List: { 10, 50, 30 } 
// Output: 
Scalar: 90

Abb.1: Verdichtung einer Liste im Rahmen einer Iteration

Die Business Logik innerhalb der Funktion List.Accumulate zum Zwecke der Summierung könnte man sich - als OOP-Pseudo-Code ausgedrückt - folgendermassen vorstellen:

// 'List.Accumulate': OOP pseudo code 
// Möglicher Einsatz als Summierungs-Funktion:  
Int Accumulate ( List list ) {
	Int result = 0; 
	ForEach ( Int item in list ) {
		result += item; 
	}
	Return result; 
}

Abb.2: List.Accumulate - OOP pseudo code

Hinweis: Bitte beachten Sie, dass List.Accumulate nicht nur zur Summierung eingesetzt werden kann, sonden für jede Art von Aggregation.

1.2. Funktions-Namen mit versteckter Iteration

Für andere Power Query Funktionen ist es nicht so offensichtlich, dass sie eine Iteration verkapseln. Beispielsweise ist die Power Query Funktion Table.SplitColumn in der Lage, aus dem Wert einer einzelnen Spalte mehrere Spalten abzuleiten. Worin besteht hier die Iteration?

Beispiel: Tabelle mit einer Spalte [Person], die geteilt werden soll:

Abb.3: Die Spalte [Person] soll geteilt werden

Die Zeichenfolge Judy, Munich in Zeile 1 kann gesplittet werden, wobei ', ' als Separator zum Einsatz kommt. Bevor die Teil-Strings von Judy, Munich in separaten Spalten plaziert werden können, muss es diese Ziel-Spalten schon geben. Der erste Schritt vor einer Teilung der Zeichenfolge besteht daher in einer Änderung des Tabellen-Schemas, indem zwei neue Spalten hinzugefügt werden. Das letzte Argument der Funktion Table.SplitColumn instruiert die Engine, die Spalten [PersonName] und [CityName] zu erstellen:

// 'Splitter.SplitTextByDelimiter' als Teil einer Schleife:
let 
	source0 = Sql.Database (".", "PowerQueryDb"), 
	customer0 = source0 {[Schema="dbo", Item="customer"]}[Data], 
	customer1 = Table.SplitColumn ( 
		customer0, 
		"Person", 
		Splitter.SplitTextByDelimiter(","), 
		{ "PersonName", "CityName" }
	)
in 
	customer1

Abb.4: 'Splitter.SplitTextByDelimiter' als Teil einer Schleife:

Konzeptioneller Schritt 1. Das Schema wird modifiziert und die beiden Ziel-Spalten werden hinzugefügt:

Abb.5: Schema Modifikation

Konzeptioneller Schritt 2. Nachdem die beiden neuen Spalten fertig für die Aufnahme von Werten sind, kommt die Iteration ins Spiel. Deren Business-Logik könnte folgendermassen in OOP-Pseudo-Code ausgedrückt werden:

 
// OOP pseudo code: Split pro Zeile  
ForEach ( Row r in customer.Rows ) {
	String[] parts = r[Person].Split(","); // Split 'Person'-column. Get string array 
	r[PersonName] = parts[0];              // Fill the 'PersonName'-column with the first array item 
	r[CityName] = parts[1];                // Fill the 'CityName'-column with the second array item 
}

Abb.6: OOP pseudo code - Split pro Zeile

Konzeptioneller Schritt 3. Nachdem die Schleife beendet ist, sind die beiden neuen Spalten gefüllt und die originale Quell-Spalte kann gelöscht werden:

Abb.7: Schritt 3

2. Lambda Expressions - Beispiel 'Table.SplitColumn'

Der folgende Abschnitt erklärt Lambda Expressions anhand der Funktion Table.SplitColumn

Die MSDN Syntax-Hilfe für Table.SplitColumn:

Table.SplitColumn ( 
	table as table, 
	sourceColumn as text,
	splitter as function,
	optional columnNamesOrNumber as any,
	optional default as any,
	optional extraColumns as any) as table
)

Abb.8: 'Table.SplitColumn' Syntax

Im Power Query Code-Beispiel von oben werden von den 6 möglichen Argumenten nur die ersten 4 verwendet:

'table'-Argument: customer
'sourceColumn'-Argument: Person
'function'-Argument: Splitter.SplitTextByDelimiter
'columnNamesOrNumber'-Argument: PersonName, CityName

Die Syntax-Hilfe informiert darüber, dass Argument 3 vom Typ function ist.

Immer dann, wenn eine PowerQuery-Funktion über ein Argument verfügt, das selbst vom Typ function ist, kann man an dieser Stelle eine Funktion wie z.B. Splitter.SplitTextByDelimiter einsetzen. Argumente vom Typ function beschränken sich jedoch nicht nur auf BuiltIn-Funktionen, sondern syntaktisch sind hier auch Lambda Expressions oder Benutzerdefinierte Funktionen erlaubt.

Allgemeine Syntax für Lambda Expressions:

(<variable>, n) => <expression>

Abb.9: Lambda expression Syntax

=> ist der Lambda-Operator. Zusammen mit den Ausdrücken auf der linken und rechten Seite ergibt sich eine Lambda-Expression. Lambda Expressions sind ein wichtiges Konstrukt in Power Query. Mit Lambda-Expressions kann komplexer Code geschrieben werden und es können Fragestellungen gelöst werden, die über die Fähigkeiten von Oberfläche oder BuiltIn-Funktionen hinausgehen. Neben der nativen Lambda-Syntax existieren auch Syntax-Alternativen, die in Internet-Artikeln häufiger erwähnt werden. Sie sind kompakter und scheinen Anfängern besser verständlich zu sein. Sie werden am Schluss des Artikels erklärt.

Eine Lambda Expression besteht aus drei Teilen: auf der linken Seite des Lambda-Operators => befinden sich runde Klammern, die keine, eine oder mehrere Variablen einschliessen. Wie bei jeder Variablen können Sie passende Namen festlegen. Dann folgt der Lambda-Operator =>. Auf der rechten Seite des Lambda-Operators befindet sich eine Expression, die auf Variablen der linken Seite zugreifen kann.

Der Code oben kann unter Verwendung der nativen Syntax folgendermassen umgeschrieben werden:

// 'Native' lambda expression Syntax: 
let 
	source0 = Sql.Database (".", "PowerQueryDb"), 
	customer0 = source0 {[Schema="dbo", Item="customer"]}[Data], 
	customer1 = Table.SplitColumn ( 
		customer0, 
		"Person", 
		(column) => Splitter.SplitTextByDelimiter(",")(column), // 'native' Lambda Expression Syntax 
		{ "PersonName", "CityName" }
	)
in 
	customer1

Abb.10: 'Table.SplitColumn' inkl. nativer Lambda Expression Syntax

3. Zusammenwirken von Iteratoren und Lambda Expressions

Schritt 1. Der Iterator - im Beispiel ist dies Table.SplitColumn - ruft in jedem Durchlauf die Lambda-Expression auf. In Durchlauf 1 wird der Wert Judy, Munich an die Lambda-Expression übergeben. Die Variable column auf der linken Seite der Lambda-Expression ist dafür zuständig, den Wert entgegenzunehmen.

Schritt 2. Der Ausdruck auf der rechten Seite des Lambda-Operators - im Beispiel ist dies Splitter.SplitTextByDelimiter - verwendet die Lambda-Variable column als Input-Argument. Im nächsten Schritt sorgt die Funktion Splitter.SplitTextByDelimiter für das Splitten der Zeichenfolge und gibt als Ergebnis ein String-Array zurück. Für die erste Zeile ist der Rückgabe-Wert { "Judy", "Munich" } zurück. Das Array wird von der äußeren Funktion Table.SplitColumn entgegen genommen und weiter verarbeitet, indem die beiden Werte in die entsprechenden neuen Spalten geschrieben werden. Dieser Vorgang wiederholt sich für jede Zeile der Tabelle.

Es gibt in Power Query eine grundsätzliche Herausforderung bei der Verwendung von Lambda Expressions. Es ist zwar klar, dass eine äußere Funktion wie Table.SplitColumn mit der Lambda-Expression interagiert, aber die Syntax-Hilfe der MSDN gibt keine Auskunft über die Details des unsichtbaren Verkehrs. Daraus entstehen folgende Unklarheiten:

Es ist nicht klar, wie viele Variablen innerhalb der Lambda Expression zur Verfügung gestellt werden müssen. Syntaktisch sind 0, 1 oder n Variablen möglich
Es ist nicht klar, welche Daten-Typen vom Iterator an die Lambda Variablen übergeben werden
Es ist nicht klar, welchen Typ die Lambda Expression zurückgeben muss

Fehler und Missverständnisse. Das nächste Beispiel zeigt eine schlechte Namensgebung für die Variable. Der Code funktioniert, aber der Variablen-Name beweist eine Art von Missverständniss. Es ist nicht eine Zeile, die an die Variable übergeben wird, sondern eine Spalte.

// Falscher Variablen-Name 'row' 
let 
	source0 = Sql.Database (".", "PowerQueryDb"), 
	customer0 = source0 {[Schema="dbo", Item="customer"]}[Data], 
	customer1 = Table.SplitColumn ( 
		customer0, 
		"Person", 
		(row) => Splitter.SplitTextByDelimiter(",")(row), 
		{ "PersonName", "CityName" }
	)
in 
	customer1

Abb.11: Falscher Variablen-Name 'row'

Es könnte eine gute Vorgehensweis darin bestehen, einer Variablen grundsätzlich zunächst einen generischen Namen zu vergeben. Z.B. könnte man immer den Variablen-Namen item vergeben. Später, wenn dann klar ist, was die Variable enthält, könnte man der Variablen einen passenderen Namen vergeben wie z.B. column.

Falsche Anzahl an Variablen:

// Falsche Anzahl an Variablen:
let 
	source0 = Sql.Database (".", "PowerQueryDb"), 
	customer0 = source0 {[Schema="dbo", Item="customer"]}[Data], 
	customer1 = Table.SplitColumn ( 
		customer0, 
		"Person", 
		(var1, var2) => Splitter.SplitTextByDelimiter(",")(var1), 
		{ "PersonName", "CityName" }
	)
in 
	customer1

Abb.12: Falsche Anzahl an Variablen

Der einzige Weg, die versteckte Signatur der Lambda Expression zu verstehen, ist Trial and Error. Zum Glück sind die BuiltIn-Funktionen logisch nachvollziehbar benannt. Die Signaturen der Funktionen können daher mehr oder weniger intuitiv erraten werden. Wenn zum Beispiel klar ist, dass die Funktion Table.SplitColumn aus einer einzelnen Zeichenfolge Werte für mehrere neue Spalten generiert, erwartet Table.SplitColumn von der Lambda-Expression offensichtlich als Rückgabe-Wert eine Liste. Und sicherlich muss die Liste für jede Zeile der äußeren Funktion die gleiche Anzahl an Werten zurückgeben. Wenn zwei neue Spalten zu füllen sind, sollte die Lambda Expression wohl eine Liste mit zwei Werten zurückgeben.

4. Alternative Syntax

Each-Syntax. Anwender tendieren dazu, die Each-Syntax zu benutzen. Bei der Each-Syntax werden Variablen-Deklaration und Lambda-Operator durch den Ausdruck Each ersetzt.

each <expression>

Die Expression kann unter Verwendung der generischen Lambda-Variable _ formuliert werden. Anders ausgedrückt, kann innerhalb der Expression die generische Lambda-Variable "_" verwendet werden. Die Variable "_" wird sozusagen implizit auf der (nicht vorhandenen) linken Seite erstellt und durch den Iterator mit Daten versorgt. Einigen Anwendern kommt die Variable sicherlich bekannt vor: in PowerShell existiert ein ähnliches Konzept.

Das Each-Schlüsselwort verdeutlicht, dass die Lambda Expression innerhalb einer Schleife aufgerufen wird.

Alternative mit each:

// Alternative Syntax mit 'each': 
let 
	source0 = Sql.Database (".", "PowerQueryDb"), 
	customer0 = source0 {[Schema="dbo", Item="customer"]}[Data], 
	customer1 = Table.SplitColumn ( 
		customer0, 
		"Person", 
		each Splitter.SplitTextByDelimiter(",")(_), 
		{ "PersonName", "CityName" }
	)
in 
	customer1

Abb.13: Alternative Syntax mit 'each'

5. Benutzerdefinierte Funktionen als Lambda Expressions

Lambda Expressions sind nützlich, wenn es schwer oder unmöglich ist, eine Aufgabe nur mit Hilfe von BuiltIn-Funktionen zu lösen. Im folgenden Beispiel wird - wie vorher schon - die Funktion Splitter.SplitTextByDelimiter innerhalb einer Lambda Expression benutzt. Jedoch sieht der Business Case zusätzlich vor, während der Split-Operation auch mögliche NULL-Werte zu ersetzen. Einige Personen-Namen enthalten NULL und einige Städte ebenfalls. Während der Split-Operation sollen die NULL-Werte durch n/a ersetzt werden.

Abb.14: Split column und Ersetzen von NULL-Werten

Verwenden eines verschachtelten let ... in ... als Teil einer Lambda Expression, um eine komplexe String-Operation zu implementieren:

// Lambda Expression und verschachteltes 'let ... in': 
let 
	source0 = Sql.Database (".", "PowerQueryDb"), 
	customer0 = source0 {[Schema="dbo", Item="customer"]}[Data], 
	customer1 = Table.SplitColumn ( 
		customer0, 
		"Person", 
		(column) => 
			let 
				colValues = Splitter.SplitTextByDelimiter(",")(column) 
				personName0 = if colValues{0} = "NULL" then "n/a" else colValues{0} 
				cityName0 = if colValues{1} = "NULL" then "n/a" else colValues{1} 
			in 
				{ personName0, cityName0 }, 
		{ "PersonName", "CityName" }

Abb.15: Lambda Expression und verschachteltes 'let ... in'

Die Lambda-Expression besteht aus mehreren Anweisungen. Drei Schritte werden im let-Block durchgeführt, um die Rückgabe des Strings vorzubereiten. Dabei wird u.a. auch die Splitter-Funktion benutzt und das Auftreten von NULL-Werten abgefangen. Der in-Block gibt am Schluss als Ergebnis das String-Array aus Person und Stadt zurück.

Abb.16: Fertiges Ergebnis

Lambda Expressions in Power Query - Teil 1 - Einführung