each
A short way to write a function that takes one argument, named _, and uses it in an expression.
each expressionReturns: A function. It is most often used inside Table.SelectRows, Table.AddColumn, List.Transform and List.Select.
What it does
each means "for each row or item, do this". It creates a function with one input, which M calls _. Inside the expression, _ is the current row or list item, and [Column] reads a field of that row. Almost every column formula in Power Query uses each, so it is worth reading it fluently.
Syntax and arguments
each expression: the expression is evaluated once for each row or item.eachby itself is the same as(_) => expression._is the current item. In a table,[Column name]reads a field of the current row.
Example
A bank file has amount and currency columns. The analyst adds a column that converts each amount into dollars using a rate that is already in the query.
let
Source = Table.FromRecords({
[Amount = 100, Currency = "USD", Rate = 1],
[Amount = 80, Currency = "EUR", Rate = 1.09]
}),
WithUsd = Table.AddColumn(Source, "AmountUsd", each [Amount] * [Rate], type number)
in
WithUsd
For each row, [Amount] * [Rate] is evaluated and placed in the new AmountUsd column. The first row gives 100 and the second gives about 87.2.
Example: a second, different use
Using _ on a plain list: List.Transform({1, 2, 3}, each _ * 10) returns {10, 20, 30}.
Things to watch
- Inside a nested
each,_refers to the innermost item. To use an outer row, name the argument, such as(row) => .... [Column]is only valid when the item is a record, which is the case for a table row.- Text and numbers do not mix. Convert before you multiply or add.
- Each
eachis a function that runs for every row, so heavy logic inside it is slow on large tables, and may stop query folding.
In business terms
Calculated columns such as net amount, days overdue, margin percentage or a status flag are written with each. Reading each as "for every row" makes most Power Query formulas easy to follow.