Do you need to update Excel data within your flow?

This can be an essential step in ensuring that your data remains current and reliable.

Updating an Excel row can be particularly useful when dealing with data sets that require regular modification. Using Power Automate to automatically update your Excel data can streamline your workflow and reduce manual errors.

This post will show you how to set up your Excel data to take advantage of the Update a row action and then use it to update Excel rows in your flow.

Key Column and Key Value in Excel Table

When working with Excel tables in Power Automate, updating a row is facilitated by the use of a key column and key value.

The key column is a designated column in the table that contains unique values, serving as an identifier for each row. The corresponding key value represents the specific unique identifier for the row you wish to update.

If you don’t already have a key column, then you will need to create such a column explicitly for the purpose of updating rows in Power Automate.

Once your key column is set up, you can make use of the Update a row action. This action requires you to provide both the key column and key value in order to identify the specific row to update.

Using a GUID for Your Key Value

If you are also adding rows via Power Automate, then you can use a GUID for your unique identifier when you create the row. This will ensure your key values are unique.

When you use the Add a row into a table action, you can set your key column to the with the guid() expression. This will set your row’s unique ID at the time the row is created. This can then be used later to update your row.

Using a GUID as your key value will eliminate the risk of duplicate IDs in your Excel table key column. This makes it easier to maintain and update your data, knowing that you have a reliable ID system for uniquely identifying each row.

How to Use the Update a Row Action

Ensure your data is organized in a table within your Excel file. Also, ensure there is a key column with unique values in your table. In this example, there is a table with an ID column containing unique values in each row.

Begin by opening Power Automate and creating or editing a flow. In your flow, search for the “Excel update a row” action, or navigate to it under the “Excel Online” connector.

Now you will be able to use the Update a row action to update any of the rows in your table based on a given ID.

1. Add the Update a row action from the Excel connector to your flow.
2. Select the Location of your SharePoint site where the Excel table you want to update is saved.
3. Select the Document Library that contains the Excel file.
4. Select the File with the file picker menu.
5. Select the Table within the file which contains the row you want to update. When you select the file, this dropdown will show you all the table objects in your file.
6. Select the Key Column within the table. Once you’ve selected the table in they previous step this dropdown will get populated with all the column names from that table. Remember this is the column in your table that contains unique values for each row.
7. Select the Key Value of the row you want to update. This example will update the row where the ID column has a 6.

Once you select the table to update in your file, the Update a row action will show you an input for each column in that table. These inputs are used to update the row with new values.

8. Enter your new updated values in the column inputs. Leave the input blank if you don’t want to change the current value. In this example, only the Year column with an ID value of 6 will be updated to the new value of 2012, the other columns will remain unchanged.

This will update the specified row with your new values!

Conclusions

The Update a row action allows you to automate the process of updating rows in an Excel file stored in SharePoint.

This action requires you to set both the key column and the key value to identify which specific row needs updating, then you can update data based on the specified key column and key value.

Did you need to update rows in Excel within your flow? Were you able to get it working? Let me know in the comments!