Table.InsertRows - Table Function

Table.InsertRows is a Power Query M function that returns a table with a list of rows inserted into the table at a given position. The function returns a table with the new rows inserted at the specified offset.

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

Syntax

Table.InsertRows(
   table as table,
   offset as number,
   rows as list,
) as table

Description

The Table.InsertRows function allows you to insert one or more rows into an existing table at a specified position. The offset parameter specifies how many rows to skip before inserting rows into the table. Note that the inserted rows must match the column types of the table.

Examples

Let’s look at some examples of the Table.InsertRows function in Power Query. We’ll start with a simple dataset that will be used in our examples. Below is a table named Source:

Table.InsertRows dataset in Power Query M

Adding A Single Row

Sometimes, you may notice missing rows in your data. In this case, the Index column is missing a value of 4. To fix this, you can use the Table.InsertRows function to insert a new row with the appropriate values after the third row. Here’s how you can do it:

Table.InsertRows( 
    Source,
    3, 
    { [Index = 4, Name = "Paul", Age = 19 ] }
)

In this expression:

Source refers to the original table.
3 specifies the position in the table where the new row will be inserted (in this case, after the third row).
The list { [Index = 4, Name = "Paul", Age = 19 ] } contains a single record, which holds the values for the new row.

After running this function, the table will look like this:

Table.InsertRows adds a single row to a table in Power Query M

Now, the Index column contains a consecutive sequence from 1 to 5, and the missing row has been successfully inserted.

Adding Multiple Rows

You can also insert multiple rows at once by providing a list of records to the Table.InsertRows function. This is useful when you need to add several rows in one go. Here’s how you add multiple rows at the same position in the table:

Table.InsertRows( 
    Source,
    3, 
    { 
       [Index = 4, Name = "Paul", Age = 19 ],
       [Index = 4, Name = "Paul", Age = 19 ] 
    }
)

In this example, we add the same record twice. They are both added after the third row in the table, with the following table as outcome:

Table.InsertRows adds multiple rows to table in Power Query M

Both new rows are inserted after the third row, and the table now contains the added data.

Full M Code

To try the full M code, you can paste the below query into the advanced editor:

let
  Source = #table(
    type table [ Index = Int64.Type, Name = text, Age = Int64.Type ],
    {
      { 1, "Rick",    32 },
      { 2, "Tom",     59 },
      { 3, "Tanya",   27 },
      { 5, "Melanie", 61 }
    }
  ),
  InsertSingleRow = Table.InsertRows( 
    Source,
    3, 
    { [Index = 4, Name = "Paul", Age = 19 ] }
),
  InsertMultipleRows = Table.InsertRows( 
    Source,
    3, 
    { 
       [Index = 4, Name = "Paul", Age = 19 ],
       [Index = 4, Name = "Paul", Age = 19 ] 
    }
)
in
  InsertMultipleRows

Learn more about Table.InsertRows in the following articles:

Manually Insert Rows to a Table in Power Query
Learn how to insert rows into a table in Power Query. Useful for creating journal entries, or adding additional information. » Read more

Other functions related to Table.InsertRows are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-insertrows