TableAction.UpdateRows

TableAction.UpdateRows is a Power Query M function that creates an action to modify rows in a table, using specified column update logic.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

TableAction.UpdateRows(
   table as table,
   columnUpdates as list,
) as any

Description

Creates an action to update the rows in table based on the set of operations in columnUpdates. The action returns a table containing the updated rows as they appear in table after the action executes.

The value of columnUpdates may be a single {columnName, columnFunction} pair or a list of such pairs. For each pair, the columnFunction is the function to apply to a row to compute the new value for the columnName column for that row.

To update a subset of the rows in table, use Table.SelectRows to apply a filter to table before using TableAction.UpdateRows.

The function raises an evaluation error if table is not updatable or if any of the operations in columnUpdates are incompatible with table.

The action raises an execution error if the operation fails.

NOTE: table may be left in a partially updated state if an execution error occurs.

Examples

Here’s an example from Ben Gribaudo:

let
  Data = Sql.Database("server", "database"){[Name = "SomeTable"]}[Data],
  UpdateAction = 
    TableAction.UpdateRows(
      Data,
     {
       {"Column1", each if [ID] < 3 then "hi" else "bye" },
       {"Column2", each "something else" }
     }
   )
in
  UpdateAction

You could even sequence multiple updates:

let
  Data = Sql.Database("server", "database"){[Name="SomeTable"]}[Data],
  FilteredTable = Table.SelectRows(Data, each [ID] < 5),
  UpdateAction1 = 
    TableAction.UpdateRows(
       Table.SelectRows(Data, each [ID] < 5), 
       {"Column1", each [Column2] }
    ),
  UpdateAction2 = 
    TableAction.UpdateRows(
       Table.SelectRows(Data, each [ID] > 8), 
       {"Column1", each "Revised" }
    )
in
  Action.Sequence({UpdateAction1, UpdateAction2})

Related articles

Learn more about TableAction.UpdateRows in the following articles:

• M Mysteries: The Mysterious Type Action—An M-Internal Means to Write Data Modifications to External Systems
  In his article “M Mysteries: The Mysterious Type Action,” Ben Gribaudo explores Power Query’s hidden capability to perform data modifications—such as inserts, updates, and deletes—using the undocumented ‘action’ type. Although Power Query is primarily designed for data retrieval and transformation, Gribaudo reveals that it possesses internal functions that can interact with external data sources to modify data. These functions, however, are not intended for general use and require the Power Query SDK for implementation. » Read more

Related functions

Other functions related to TableAction.UpdateRows are:

• TableAction.DeleteRows
• TableAction.InsertRows
• TableAction.Tee

Contribute » | Contributors: Rick de Groot