Moving forward with our series about how to work with Excel Tables in Power Automate, we investigate how to insert data into Excel Tables from Power Automate. It includes either inserting new rows into a Table or updating existing records.

Other articles from this series

Integrating Power Automate and Excel Tables

Reading data from Excel Tables in Power Automate

Use case: Office List Table

For this post, we will use the ‘Office_List’ Table, created in the first article of the series. The table contains 5 records (rows) about a company offices locations, in a total of 4 columns: an Id column, the office city, the office country and the quantity of employees at office:

Inserting Data to Excel Table in Power Automate

There is a specific action to insert a record to an Excel Table with Power Automate: the ‘Add row into a table’. The action structure is simple and very similar to ‘List rows from a table’: you need to specify the file location that contains the table and select it from a dropdown. After that, the action will display one input field per each table column (highlighted in yellow); these fields will vary according to the table:

After running the flow, the record will be included as a new row in the Excel Table:

Updating values in an Excel Table with Power Automate

The process to update rows in Excel tables will require a key column. As we saw in the previous post of this series, a key column is a table column that contains exclusively unique values (non-duplicated), and it is used to identify each table record in an individual way. In our example, the best candidate for a key column is the “Office Id”.

To update a record, use the ‘Update a row’ action: the input fields will vary according to the columns that exists in your table. The action requires a key column and a key value (highlighted in yellow in the image below), where you may identify which column containing only unique values will be used as reference and which unique identifier you want to use to find the record to be updated (in our case, the ‘Office Id’ column and the record with Id 1):

The key column is used to filter the table records, allowing the user to isolate the row that need to be updated. Ideally, the key column may not have repeated values: in our example, the ‘Employees’ or the ‘Country’ columns would not be nice candidates to be used as keys, as they have repeated values (‘USA’ and 14, respectively). After identify the key column, Power Automate will require a ‘key value’, which is the value that will identify the record to be updated.

You don’t need to fill all fields corresponding to each table column, but only those that you want to effectively update. In our example, we will just change the office ‘City’, updating it to ‘Denver’ (highlighted in blue).

As a result, the record with ‘Office Id’ 1 has its city updated to ‘Denver’:

Updating more than one record in an Excel table

The ‘Update a row’ action requires a key column and its corresponding key value to identify the record for updating. If the key value is duplicated in the column, only one record will be updated. However, there are instances where we need to change multiple values based on a condition. For example, suppose we want to identify ‘USA’ in the ‘Country’ column as ‘United States’ due to a business rule change.

In real-world scenarios with hundreds or thousands of records per table, individually passing the ‘Office Id’ to ‘Update a row’ actions becomes impractical. An efficient solution is to first read the table records, filter them based on the desired condition (e.g., ‘Country’ column equals ‘USA’), loop through the filtered records, and update each one with the new values. This approach allows for bulk updates and streamlines the process.

The first step is to retrieve all records from the table filtered by ‘Country’. For doing this, we use the ‘List rows present in a table’ action and pass a filter query to limit the results (there is a section exploring this operation in our article about how to read data from Excel Tables). The filter query will looks like this:

The body of the action’s raw outputs (if you don’t know how to access raw outputs, check this post) will be an array containing all Table records which ‘Country’ is equal to ‘USA’:

As the output is an array, we can access each of its elements and perform a set of actions by using a loop (check this article for more details about how to work with loops in arrays), which in this case will be the “Apply to each”. The input will be the value property from the ‘List rows present in a table’ action (highlighted in yellow), and inside the block we will add an ‘Update a row’ action. In the update action, set the key column as ‘Office Id’ and, as key value, the dynamic content from the ‘List rows present in a table’ Office Id (highlighted in blue). Finally, the only value that will be updated is the ‘Country’, which will values ‘United States’ (highlighted in green):

In summary, we are returning the Table rows with ‘Country’ equals to ‘USA’ and looping all these records (only 2, in our case) to perform a specific action to each of them: update its ‘Country’ to ‘United States’. After run the flow, the table will look like this:

Conclusion

In this post, we explored how to insert and update data in Excel Tables using Power Automate. The ‘Add row into a table’ action allowed us to seamlessly add new records, while the ‘Update a row’ action facilitated the modification of existing entries. By identifying key columns and using filters and loops, we efficiently updated multiple records based on specific conditions, streamlining data management tasks.

Mastering these techniques empowers users to automate workflows and optimize processes within Excel Tables. With the integration of Power Automate, users can work more efficiently, reduce errors, and gain valuable insights from their data.