Table.TransformRows
Applies a function to each row, given as a record, and returns a list of the results.
Table.TransformRows(table as table, transform as function) as listReturns: 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
- table: the table to read. Required.
- transform: a function that takes one row and returns a value. Write it as
(row) => ..., and read columns withrow[ColumnName]. Required.
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
- The result is a list. Turn it back into a table with Table.FromList, Table.FromRecords or Table.FromRows, or use Table.AddColumn if you need a column.
- The function sees one row at a time. Column types still matter, so type the columns first.
- Query folding: a row function does not fold to SQL. Power Query runs it on the loaded rows, so filter and select first.
- Keep the logic readable. A long row function is hard for the next person to audit.
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.