Part 1 of this series shows how lambda expressions work within Power Query iterators. Lambda expressions placed inside an Power Query function will be executed repeatedly. With each call, the iterator passes different data to the lambda expression.

Like SQL, Python, DAX etc., Power Query also offers a set of iterator functions. Often the function names suggest that there is a loop hiding inside, sometimes not. And in some cases it's even hard to understand the specific function as an iterator.

Function name with obvious reference to a loop. The name of an iterator function, such as "List.Accumulate", makes it clear that you pass a list to the function and receive a single item back. It is obvious that a loop is run on the input list to compute the result.

The business logic within the function could be thougt of as follows:

Hidden iteration. For other Power Query functions it's not so obvious that they encapsulate an iteration. For example, the Power Query function "Table.SplitColumn" is able to derive multiple columns from the value of a single column. What would the iteration consist of?

Example - table where a column is to be split:

The string "Judy, Munich" in line 1 can be split using ", " as a separator. Before the scalar values, "Judy" and "Munich", can be placed in new columns, the target columns must already exist. The first step before splitting the string must therefore be to change the table schema by adding two new columns. The last argument of the "Table.SplitColumn" function instructs the engine to create columns "PersonName" and "CityName":

General syntax for lambda expressions:

“=>” is the lambda operator. Together with the expressions on the left and right, you have a lambda expression, an important concept of the Power Query language. Complex code can be written with lambda expressions and problems can be solved that go beyond the capabilities of the Power Query User Interface or BuiltIn functions. In addition to the native Lambda syntax, there are also syntax alternatives that are generally used more frequently. They are more compact and seem easier for beginners to understand. They are explained at the end of the article.

A lambda expression consists of three parts: on the left side of the lambda operator "=>" there are round brackets that enclose zero, one or more variables. As with any variable, you are free to choose an appropriate name. On the right side of the lambda operator is an expression that can use the variable(s) on the left.

The code above can be rewritten using the native syntax as follows:

Step 1: The iterator "Table.SplitColumn" calls the lambda expression in each run. In pass 1, the value "Judy, Munich" is passed to the lambda expression. The variable "column" on the left side of the lambda expression is responsible for receiving the value.

Step 2: The expression on the right side of the lambda operator - "Splitter.SplitTextByDelimiter" - takes the lambda variable "column" as an input argument. In the next step, the "Splitter.SplitTextByDelimiter" function splits the string and returns a string array as the result - for the first line in the example { "Judy", "Munich" }. The outer function "Table.SplitColumn" receives the array and writes the array items into the appropriate new columns. This process is repeated for each row of the table.

There is a fundamental challenge in using lambda expressions in Power Query. It is clear that an outer function such as "Table.SplitColumn" can pass values to the lambda variables and it is also clear, that it is able to get values back from the lambda expression. However, the MSDN syntax help does not provide detailed information about this traffic:

Mistakes and misunderstandings - Bad variable name. The next example shows that the developer has choosen a bad name for the variable. The code works, but the variable name exposes some kind of misunderstanding. It's not the row that's passed to the variable, it's a single column.

It might be a good practice to always give a variable a generic name first when you start coding. E.g. you could always place the variable name "item" on the left side. Later, when you are sure, what kind of data is carried around through the variable, you could give the variable a more appropriate name, such as "column".

The only way to understand the lambda expression's hidden signature is by trial and error. Luckily the BuiltIn functions are named in a manner that makes them easy to understand. It is therefore intuitive to more or less guess the signature. For example, if a function like "Table.SplitColumn" generates values for multiple new columns from a single string, "Table.SplitColumn" obviously expects the lambda expression to return a list. And surely the list must return the same number of values for each row of the outer function. If there are two new columns to fill, the lambda expression should of course return a list of two values.

Each-Syntax. Users tend to use the "Each-syntax". With the "Each syntax", variable declaration "(...)" and lambda operator "=>" are replaced by the expression "Each". The "Each" keyword underlines that the lambda expression is called repeatedly.

The generic variable "_" can be used on the right side. The variable "_" is implicitly created on the left side and filled by the iterator. The variable will probably look familiar to some users: a similar concept of an auto-created and auto-filled variable named "_" exists in PowerShell too.

Lambda expressions come into play when you are not able to solve a task using builtin functions. In the following example, the builtin function "Splitter.SplitTextByDelimiter" is used within a lambda expression. The business case requires us to not only carry out the splitting, but additionally to replace possible NULL values during the operation. Some person names contain NULL and some cities contain NULL as well. During the split operation, these NULL values should be replaced with "n/a".

The right side lambda expression consists of several statements. Three steps are performed in the "let"-block to compute the return value successively. Among other things, the splitter function is used and the occurrence of NULL values is checked. At the end, the "in"-block passes the string array of person and city to the iterator.