How to Insert a Row Into a Data Table in Power Automate Desktop

Most desktop flows I build end up working with a data table at some point. It might hold rows read from an Excel file, a table scraped from a web page, or the results of a SQL query. And sooner or later, the flow needs to add a row to that table: a new record, a total at the bottom, or a copy of a row that met some condition.

Power Automate Desktop gives you a dedicated action for this, and there’s also a quick expression you can use instead. In this tutorial, I’ll create a small product table and show you three ways to insert rows into it, along with the error you’ll hit if the values don’t line up with the columns.

Quick answer: Add the Insert row into data table action, select your data table variable, set Into location to End of data table (or Before row index to insert it higher up), and enter the values in New value(s) as a list, for example %['Charger', 'P1006', 65, 'Electronics', 'True']%. Give one value for every column, in the same order as the columns.

What is a data table in Power Automate Desktop?

A data table is a variable that holds rows and columns, much like a small Excel worksheet stored in memory. Both rows and columns are numbered from 0, so the first row is row 0 and the first column is column 0 (Microsoft Learn: variable data types). You’ll get data tables from actions such as Read from Excel worksheet, Extract data from web page and Execute SQL statement, or you can build one yourself.

For this tutorial, I’ll create a table of products with five columns: Product Name, Product ID, Product Stock, Category and In Stock.

Step 1: Create a data table

Open Power Automate Desktop, select + New flow, give the flow a name and select Create. The flow designer opens, ready for you to add actions.

In the Actions pane, search for data table and drag Create new data table into the flow. The action produces a variable called DataTable; I renamed mine to ProductDetails so it’s easier to recognise later.

Select Edit to open the table editor. Use the + at the top right to add columns and the + at the bottom to add rows, then rename the column headers and type in your values. I added four products. When you’re done, select Save.

Run the flow and double-click ProductDetails in the Variables pane. You’ll see the table exactly as you entered it:

If you prefer typing to clicking, you can create the same kind of table in a single Set variable action. The first row, marked with ^, becomes the column headers:

%{^['Product Name', 'Product ID', 'Product Stock', 'Category', 'In Stock'], ['Laptop', 'P1001', 20, 'Electronics', 'True'], ['Mouse', 'P1002', 150, 'Accessories', 'True']}%

Method 1: Insert a row at the end with the Insert row action

This is the method I use most, because it’s easy to read when you come back to the flow months later. Add the Insert row into data table action after the step that creates the table, and fill in its settings:

• Data table: select your variable with the {x} button, for example %ProductDetails%.
• Into location: choose End of data table to add the row at the bottom.
• New value(s): enter the new row’s values in square brackets, in the same order as the columns: %['Charger', 'P1006', 65, 'Electronics', 'True']%.

Select Save and run the flow. When you open the variable again, the charger appears as a new row at the bottom of the table:

Method 2: Insert a row at a specific position

The same action can also insert a row higher up in the table. Change Into location to Before row index, and enter the position in Row index. Because rows are numbered from 0, a row index of 0 puts the new row at the very top, and 2 inserts it just before the third row.

If you need to know how many rows the table has, for example to insert a row before the last one, use %ProductDetails.RowsCount%.

Method 3: Add a row with the Set variable action

There’s also a shortcut that doesn’t need the Insert row action at all. In a Set variable action, you can take the data table, add a plus sign, and put the new row’s values in brackets:

%ProductDetails + ['Charger', 'P1006', 65, 'Electronics', 'True']%

When you run the flow and open the variable, the new row sits at the end of the table. In my case it’s row 5, because the table already had rows 0 to 4:

This expression always adds the row at the end. If you need to insert it somewhere else, use Method 2.

How to fix “The number of values provided doesn’t match the columns”

The most common problem with inserting rows is a mismatch between the values and the columns. If you give four values to a table with five columns, the action stops with this error:

The number of values provided to be added (4) doesn't match the columns of the data table (5).

To fix it, count the columns in your table and make sure the new row has exactly one value for each of them, in the same order. If you want to leave a column empty, put an empty text value '' in its place rather than leaving it out.

How to insert rows in a loop

In real flows, you often build one table from another. For example, you might want a second table that contains only the products running low on stock. Here’s how I do it:

1. Create a second data table, LowStock, with the same columns as the first.
2. Add a For each loop and set Value to iterate to %ProductDetails%. On each pass, the loop puts one row into %CurrentItem%.
3. Inside the loop, add an If action that checks %CurrentItem['Product Stock']% is Less than 30.
4. Inside the If, add Insert row into data table with Data table set to %LowStock% and New value(s) set to %CurrentItem%.

You don’t need to rebuild the row value by value, because New value(s) accepts a whole data row as well as a list. Passing %CurrentItem% copies the row across as it is.

Other data table actions you’ll use alongside Insert row

Once you’re comfortable inserting rows, these related actions cover most of what you’ll need to do with a data table:

Action	What it does
Update data table item	Changes the value in one cell. You set the Column (name or index), the Row and the New value.
Delete row from data table	Removes the row at the Row index you specify.
Insert column into data table	Adds a new column, either at the end or before a column index.
Retrieve data table column into list	Copies one column into a list variable.
Find or replace in data table	Searches for text across the table or in one column, and can replace it.
Filter data table and Sort data table	Keep only the rows that match your rules, or sort the rows by a column.
Delete duplicate rows from data table	Removes rows that repeat exactly.

To read a single cell, use the row and column numbers, such as %ProductDetails[1][0]% for the second row and first column, or the column name, such as %ProductDetails[1]['Product Name']%. Microsoft’s variables actions reference lists every data table action with its settings.

A note for Power Fx flows: if you turned on Power Fx when you created the desktop flow, the syntax is different. Expressions start with = instead of being wrapped in %, and rows and columns are numbered from 1, for example =Index(Index(ProductDetails, 2), 1). All the examples above are for flows without Power Fx, which is the default.

If you’re working with data tables and Excel in desktop flows, these tutorials will help next:

• Update Data Table Items using Power Automate Desktop
• How to Get Values from Data Table using Power Automate Desktop?
• Variables in Power Automate Desktop
• Retrieve Excel Column Values Into List Using Power Automate Desktop
• Extract Data from an Excel using Power Automate Desktop
• Power Automate Desktop Flow Examples [5 Practical Examples]

Frequently Asked Questions