Downloads
Power Query MLanguageBeginner

each

A short way to write a function that takes one argument, named _, and uses it in an expression.

Syntax
each expression

Returns: 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

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

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.

Related functions

All Power Query M functions · View as Markdown · M basics