Blog Article & Copyright by Lukas Hillesheim
In part 1 of this series i mentioned that the MSDN does not provide information about the concrete (data) traffic between the lambda expression and the iterator that the lambda expression is part of. Part 2 of this series shows three examples of how Power Query iterator and lambda expression interact with each other.
Regarding the interaction of iterator and lambda expression, the following questions arise:
- - How many variables does the lambda expression have to provide in order to receive the data from the iterator (0, 1, n)
- - Which data types are passed from the iterator to the lambda expression?
- - Which data type (scalar, array, object etc.) must be returned from the lambda expression to the iterator?
- - What does the iterator do with the return value of the lambda expression (iterator business logic)?
- - What data does the iterator return?
Calling a lambda expression within an iterator:
<iterator> ( ( <variable>, n ) => <lambda_expression> )
The examples in this article use these three iterators:
- - List.Transform
- - Table.TransformRows
- - Record.TransformFields
1. General
MSDN syntax help on the three example functions:
- List.Transform (
- list as list,
- transform as function) as list
- Table.TransformRows(
- table as table,
- transform as function) as list
- Record.TransformFields(
- record as record,
- transformOperations as list) as record
All three iterators implement some kind of ETL logic. "ETL" stands for
- - E(xtract). Extraction of the data from the source
- - T(ransform). Apply logic to modify the data. Deriving new data based on the source data
- - L(oad). Load the data into the target
The lambda expression is responsible for the "T", the transformation part.
Iteration. All three functions are iterators. From the OOP point of view, they all implement a loop:
- - List.Transform(). Foreach ( item i in <list> ) { … }
- - Table.TransformRows(). Foreach ( Row r in <table>.Rows ) { … }
- - Record.TransformFields(). Foreach ( Field f in <record>.Fields) {...}
Result of type "iterable". All three functions return a list object:
- - List.Transform() as list
- - Table.TransformRows() as list
- - Record.TransformFields() as record. A list of fields
Argument of type "function". All three functions have a function argument:
- - List.Transform ( … , transform as function ). Individual function or lambda expression that performs a transformation task.
- - Table.TransformRows ( … , transform as function ). Individual function or lambda expression that performs a transformation task.
- - Record.TransformFields ( …, transformOperations as list, … ). Several functions or lambda expressions that perform a transformation task per field.
2. List.Transform
List.Transform - Example 1.
Figure 1 - List.Transform with simple lambda expression
Step 1 - Declaration of the variable "list0". A list of three numbers is declared.
Step 2 - Declaration of the variable "list1". On the left: Each number of the list is successively assigned to the lambda variable. On the right: the lambda expression multiplies the number by 10. The result is inserted into the result set.
Figure 2 - List.Transform with simple lambda expression.Result.
List.Transform - Example 2.
Figure 3 - List.Transform with a constant expression
The input is a list of numbers, the loop runs three times. In each loop pass, the constant value "test" is inserted into the result set.
Figure 4 - List.Transform with a constant expression. Result.
List.Transform - Example 3.
Figure 5 - List.Transform: result is a list of records.
The input is a list of numbers, the result is a list of records. Figure 7 shows Record 1
Figure 6 - List of records.
Figure 7 - Record 1.
List.Transform - Example 4.
Figure 8 - List.Transform: the input is a list of records.
With each pass the lambda expression receives a record from the iterator and returns a record to the iterator. Both output columns, "Id" and "Name", derive their value from the respective input record.
Figure 9 - List.Transform: the output is a list of records.
Figure 10 - Record 1.
List.Transform - Example 5.
Figure 11 - List.Transform: the input is a list of lists.
The input consists of a list of lists. The first list is a list of numbers between 1 and 3, the second list is a list of letters from "A" to "G" and the third list is a list of date values between 7/1/2022 to 7/10/2022. Taking this fact into account, the lambda variable is named "list". Within each pass the lambda expression computes the number of members that the respective list-item contains.
Figure 12 - List.Transform: the output is a list of scalar values each representing the count of list items.
3. Table.TransformRows
Table.TransformRows - Example 1
Figure 13 - Table.TransformRows: the input is a table with its rows.
The input, "table0", is a table with three rows.
Please keep in mind that PowerQuery distinguishes between "row" and "record". A "row" is a member of a table. A "record" is an object independent of a table. A record can, but does not necessarily have to be part of a list. The difference between the very similar objects, "row" and "record", is that all rows in a table are required to follow the same schema; i.e. the same number of columns and the same types. In contrast to this: each record of a list of records can follow a different scheme.
Step 1 - Declaration of the table variable "table0". Content of the table:
Figure 14 - Table.TransformRows: content of variable ‘table0’
Step 2 - declaration of the variable "list0". A new record is inserted into the output for each item of the input. The result is a list of records:
Figure 15 - Table.TransformRows: the result is a list of records.
Content of the first record:
Figure 16 - Table.TransformRows: first record.
Step 3 - Declaration of the variable "table1". One additional task with using "Table.TransformRows" is transforming the list of records back into a table. This task is solved with "Table.FromRecords":
Figure 17 - Table.TransformRows: Re-convert record list into table.
4. Record.TransformFields
Record.TransformFields - Example 1.
Figure 18 - Record.TransformFields: a list of lambda expressions is applied
Step 1 - Declaration of variable "record0". A record with four columns is declared.
Step 2 - Declaration of variable "record1". The second argument of "Record.TransformFields" is a list of lambda expressions. Each individual lambda expression is responsible for performing a transformation on a specific column. The respective column is mentioned of the left side. In the example, the columns are "Amount" and "Date". Any column of the input record not mentioned in the list of transformations is included in the result record without modification. This applies to "CustId" and "CustName".
Figure 19 - Record.TransformFields: 2 fields were transformed (Amount := amount gross, Date := due date).
The MSDN syntax help contains an example using the "Record.TransformFields" function to modify the types of columns:
Figure 20 - MSDN syntax help for 'Record.TransformFields'
The expression "Number.FromText" can be thought to be expanded by the Power Query Engine to the following lambda expression:
(fieldValue) => Number.FromText (fieldValue)