Table.InsertRows
TableInserts a list of row records into the table at the given offset.
Syntax
Table.InsertRows(table as table, offset as number, rows as list) as tableParameters
| Name | Type | Required | Description |
|---|---|---|---|
table | table | Yes | The table to insert rows into. |
offset | number | Yes | The zero-based index at which to insert the new rows. |
rows | list | Yes | A list of records to insert, where each record field corresponds to a column name. |
Return Value
table — The table with the new rows inserted at the specified position.
Remarks
Table.InsertRows inserts a list of new row records at a specific zero-based position within a table. All existing rows at and after the offset are shifted down to accommodate the new rows. Each record in the rows list should have fields corresponding to the table's column names — missing fields default to null and extra fields are ignored.
To append rows at the end of the table, set offset to Table.RowCount(table). To prepend rows at the start, use offset = 0. Unlike Table.Combine, Table.InsertRows gives positional control over where new rows land in the table.
Table.InsertRows is a position-based operation and does not fold to data sources. For large tables, prefer Table.Combine or source-side UNION operations to avoid loading the full table into memory.
Examples
Example 1: Insert a missing order row into its correct position
let
Sales = #table(
{"OrderID", "CustomerName", "Product", "Amount"},
{{1,"Alice","Widget A",100},{3,"Charlie","Widget C",150},{4,"Diana","Widget A",175}}
)
in
Table.InsertRows(
Sales,
1,
{[OrderID = 2, CustomerName = "Bob", Product = "Gadget B", Amount = 200]}
)OrderID | CustomerName | Product | Amount | |
|---|---|---|---|---|
| 1 | 1 | Alice | Widget A | 100 |
| 2 | 2 | Bob | Gadget B | 200 |
| 3 | 3 | Charlie | Widget C | 150 |
| 4 | 4 | Diana | Widget A | 175 |
Example 2: Append a row at the end using RowCount as offset
let
Customers = #table(
{"CustomerID", "Name", "City"},
{{1,"Alice","New York"},{2,"Bob","Chicago"},{3,"Charlie","Houston"}}
),
NewRow = [CustomerID = 4, Name = "Diana", City = "Seattle"]
in
Table.InsertRows(Customers, Table.RowCount(Customers), {NewRow})CustomerID | Name | City | |
|---|---|---|---|
| 1 | 1 | Alice | New York |
| 2 | 2 | Bob | Chicago |
| 3 | 3 | Charlie | Houston |
| 4 | 4 | Diana | Seattle |
Example 3: Insert multiple rows at position 0 (prepend)
let
Products = #table(
{"ProductID","ProductName","Price"},
{{3,"Widget C",15.00},{4,"Gadget D",75.00},{5,"Thingamajig E",120.00}}
)
in
Table.InsertRows(
Products,
0,
{[ProductID = 1, ProductName = "Widget A", Price = 25.00],
[ProductID = 2, ProductName = "Gadget B", Price = 50.00]}
)ProductID | ProductName | Price | |
|---|---|---|---|
| 1 | 1 | Widget A | 25 |
| 2 | 2 | Gadget B | 50 |
| 3 | 3 | Widget C | 15 |
| 4 | 4 | Gadget D | 75 |
| 5 | 5 | Thingamajig E | 120 |