Table.TransformRows Function in Power Query

The Table.TransformRows function transforms the rows from a table using a transform function and return the result as list.

Syntax

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

The function has the following parameters:
• table: The input table to transform.
• transform: A function that defines how each row (record) is modified. The function accepts a single row (as a record) and returns a transformed result (often another record).
Returns: A list of transformed rows. Each item in the list corresponds to a row in the original table.

Key Features

Row-Wise Operations:

Applies a custom function to every row in the table.
Each row is treated as a record, allowing access to column values via row[ColumnName].

Flexibility:

The transform function can return any data type (e.g., a modified record, a single value, or a nested structure).
To convert the output back to a table, use Table.FromRecords(...) function.

Order Preservation:

The order of rows in the resulting list matches the original table.

Use Cases:

Adding/removing columns conditionally.
Complex calculations involving multiple columns.
Custom transformations not achievable with built-in functions like Table.AddColumn.

Example:

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Age = 56], 
      [CustomerID = 2, Name = "Katrina", Age = 43], 
      [CustomerID = 3, Name = "Alia", Age = 90], 
      [CustomerID = 4, Name = "Vicky", Age = 19], 
      [CustomerID = 5, Name = "Mohini", Age = 16], 
      [CustomerID = 6, Name = "Meenakshi", Age = 64], 
      [CustomerID = 7, Name = "Esha", Age = 54], 
      [CustomerID = 8, Name = "Anjali", Age = 55]
    }
  ),

Return = Table.TransformRows(MyTable, each _)
in
    Return

The output of the above code is shown below:

Table.TransformRows function in Power Query

Example: Transform the rows of a table into a list of names.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Age = 56],
      [CustomerID = 2, Name = "Katrina", Age = 43],
      [CustomerID = 3, Name = "Alia", Age = 90],
      [CustomerID = 4, Name = "Vicky", Age = 19],
      [CustomerID = 5, Name = "Mohini", Age = 16],
      [CustomerID = 6, Name = "Meenakshi", Age = 64],
      [CustomerID = 7, Name = "Esha", Age = 54],
      [CustomerID = 8, Name = "Anjali", Age = 55]
    }
  ),
  Return = Table.TransformRows(MyTable, each [Name])
in
  Return

The output of the above code is shown below:

Example: Let’s add a field to each row in the table.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Age = 56],
      [CustomerID = 2, Name = "Katrina", Age = 43],
      [CustomerID = 3, Name = "Alia", Age = 90],
      [CustomerID = 4, Name = "Vicky", Age = 19],
      [CustomerID = 5, Name = "Mohini", Age = 16],
      [CustomerID = 6, Name = "Meenakshi", Age = 64],
      [CustomerID = 7, Name = "Esha", Age = 54],
      [CustomerID = 8, Name = "Anjali", Age = 55]
    }
  ),
  Return = Table.TransformRows(MyTable, (row) => Record.AddField(row, "IsSeniorCitizen", row[Age] >= 60))
in
  Return

The output of the above code is shown below:

To convert the output back to a table, use Table.FromRecords(...) function.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Age = 56],
      [CustomerID = 2, Name = "Katrina", Age = 43],
      [CustomerID = 3, Name = "Alia", Age = 90],
      [CustomerID = 4, Name = "Vicky", Age = 19],
      [CustomerID = 5, Name = "Mohini", Age = 16],
      [CustomerID = 6, Name = "Meenakshi", Age = 64],
      [CustomerID = 7, Name = "Esha", Age = 54],
      [CustomerID = 8, Name = "Anjali", Age = 55]
    }
  ),
  Return = Table.TransformRows(MyTable, (row) => Record.AddField(row, "IsSeniorCitizen", row[Age] >= 60)),
  final_output = Table.FromRecords(Return)
in
  final_output

The output of the above code is shown below:

Previous Next