Downloads
Power Query MTable: columnsAdvanced

Table.TransformRows

Applies a function to each row, given as a record, and returns a list of the results.

Syntax
Table.TransformRows(table as table, transform as function) as list

Returns: A list with one result for each row.

What it does

Table.TransformRows runs a function on every row. Each row arrives as a record, so you can read any column by name. The result is a list, not a table. Use it when a row needs logic that is hard to express in one column, such as building a text line from several fields. For a new column of values, Table.AddColumn is usually clearer.

Syntax and arguments

Example

A list of open items is turned into a line of text for each item, so the lines can be pasted into an email to the controller.

let
    Source = Excel.CurrentWorkbook(){[Name = "OpenItems"]}[Content],
    Lines = Table.TransformRows(Source, (row) => row[Vendor] & ": " & row[Reference]),
    AsTable = Table.FromList(Lines, Splitter.SplitByNothing(), {"Line"})
in
    AsTable

The function builds one text line per row. The list of lines is then placed back in a one-column table.

Example: a second, different use

A row is classed as needing review when it fails a combination of tests across several columns. The result is a list of true or false values.

let
    Source = Excel.CurrentWorkbook(){[Name = "Payments"]}[Content],
    Flags = Table.TransformRows(Source, (row) => row[Amount] > 10000 and row[Approved] = null)
in
    Flags

Things to watch

In business terms

Row logic is common when a finance team builds memo lines, approval flags or export text from several fields. Writing it once in the query keeps the wording and the rule the same from month to month.

Related functions

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