Table.ReplaceRows Function in Power Query

The Table.ReplaceRows function replaces the specified count of rows, by the given rows in the input table after the specified offset.

Syntax

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

The function has the following parameters:

table: The table where the replacement is performed.
offset: The number of rows to skip before making the replacement.
count: The number of rows to replace.
rows: The list of row records to insert into the table at the location specified by the offset.

Example:

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [EmployeeID = 1, Name = "Ashish", Science_Marks = 56, Math_Marks=65],
      [EmployeeID = 2, Name = "Katrina", Science_Marks = 64, Math_Marks=65],
      [EmployeeID = 3, Name = "Alia", Science_Marks = 38, Math_Marks=65],
      [EmployeeID = 6, Name = "Meenakshi", Science_Marks = 29, Math_Marks=65],
      [EmployeeID = 7, Name = "Esha", Science_Marks = 50, Math_Marks=80],
      [EmployeeID = 8, Name = "Anjali", Science_Marks = 38, Math_Marks=38]
    }
  ), 
  return = Table.ReplaceRows(MyTable, 1, 3, { 
      [EmployeeID = 4, Name = "Vicky", Science_Marks = 90, Math_Marks=60],
      [EmployeeID = 5, Name = "Mohini", Science_Marks = 28, Math_Marks=65]} 
      )
in
  return

The output of the above formula is shown below:

Table.ReplaceRows function in Power Query

Previous Next