TableAction.InsertRows

Updated on
Rick de Groot

TableAction.InsertRows is a Power Query M function that creates an action to insert rows into a table, returning the inserted rows as they appear in the updated table.

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

Syntax

TableAction.InsertRows(
   table as table,
   rowsToInsert as table,
) as any

Description

Creates an action that inserts rowsToInsert into table. The action returns a table containing the inserted rows as they appear in table after the action executes.

Target-specific default values are used for the columns in table that are not specified in rowsToInsert.

The function raises an evaluation error if table is not updatable or if rowsToInsert is not compatible 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],
  InsertAction = TableAction.InsertRows(
    Data, 
    #table(type table [ID = number, Column1 = text, Column2 = text], 
    {{1, "1","2"}})
  )
in
  InsertAction

Learn more about TableAction.InsertRows 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

Other functions related to TableAction.InsertRows are:

Contribute » | Contributors: Rick de Groot

2023-2026 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy