Table.ReplaceRows

Table

Replaces a specified number of rows starting at an offset with a new set of row records.

Syntax

Table.ReplaceRows(table as table, offset as number, count as number, rows as list) as table

Parameters

Name	Type	Required	Description
`table`	`table`	Yes	The table to modify.
`offset`	`number`	Yes	The zero-based index of the first row to replace.
`count`	`number`	Yes	The number of rows to remove starting at offset.
`rows`	`list`	Yes	A list of records to insert in place of the removed rows.

Return Value

table — The table with the specified rows replaced by the new rows.

Remarks

Table.ReplaceRows removes a block of count rows starting at zero-based offset and inserts the new rows list in their place. The number of replacement rows does not need to equal count — you can replace 2 rows with 1, or 1 row with 3. Each record in rows must have fields corresponding to the table's column names; missing fields default to null.

Table.ReplaceRows is the combination of Table.RemoveRows followed by Table.InsertRows at the same position, performed as a single operation. It is useful for correcting known bad rows in a dataset or swapping out rows identified by position (often in combination with Table.PositionOf).

Table.ReplaceRows does not fold to data sources. For large tables, prefer source-side MERGE or UPDATE operations. When replacing by matching content rather than by position, use Table.ReplaceMatchingRows instead.

Examples

Example 1: Correct a single erroneous order record

let
    Sales = #table(
        {"OrderID","CustomerName","Product","UnitPrice","Quantity"},
        {{1,"Alice","Widget A",25.00,4},{2,"Bob","Gadgt B",50.00,2},{3,"Charlie","Widget C",15.00,10}}
    )
in
    Table.ReplaceRows(
        Sales, 1, 1,
        {[OrderID = 2, CustomerName = "Bob", Product = "Gadget B", UnitPrice = 50.00, Quantity = 2]}
    )

Result

	OrderID	CustomerName	Product	UnitPrice	Quantity
1	1	Alice	Widget A	25	4
2	2	Bob	Gadget B	50	2
3	3	Charlie	Widget C	15	10

Example 2: Replace two rows with a single consolidated row

let
    Summary = #table(
        {"Region","Total"},
        {{"East",100},{"North",150},{"West",200},{"South",50}}
    )
in
    Table.ReplaceRows(Summary, 1, 2, {[Region = "North+West", Total = 350]})

Result

	Region	Total
1	East	100
2	North+West	350
3	South	50

Example 3: Dynamically locate a row, then replace it

let
    Products = #table(
        {"ProductID","ProductName","Price"},
        {{1,"Widget A",25.00},{2,"Gadget B",50.00},{3,"Widget C",15.00},{4,"Gadget D",75.00}}
    ),
    Pos    = Table.PositionOf(Products, [ProductID = 3], Occurrence.First, "ProductID"),
    Result = if Pos >= 0
             then Table.ReplaceRows(Products, Pos, 1, {[ProductID = 3, ProductName = "Widget C v2", Price = 18.00]})
             else Products
in
    Result

Applied Steps

The final output — since Pos is 2 (>= 0), replaces 1 row at position 2 with an updated record for Widget C v2 at price 18.00.

	ProductID	ProductName	Price
1	1	Widget A	25
2	2	Gadget B	50
3	3	Widget C v2	18
4	4	Gadget D	75

Compatibility

✓ Power BI Desktop✓ Power BI Service✓ Excel Desktop✓ Excel Online✓ Dataflows✓ Fabric Notebooks