The integration between Power Automate and Excel is fantastic, and we can do a lot with the actions available to us. Today we’ll check the “Excel Update a row” action that does exactly what you expect. Updates a row in Excel with the value(s) that you define.
Before we start
Before we start, it’s important to note that Power Automate requires that the information is inside a table in Excel. There are multiple reasons why Microsoft did this, but the most important is that Excel files are a “sandbox” for us to play, and Power Automate needs an identifier to understand what data we want. Also, it makes it easier to insert, update and delete columns.
Here’s how to do it. I’m using Excel for macOS, but it works the same way in the Windows version. So first, select the data.
Then press “Insert” and then “Table”
Don’t forget to select “headers” if your data indeed has headers.
Here’s what it looks like.
Where to find it?
To find it you can search for “Excel update a row” action or by going to “Excel Online”.
Select the “update a row” action
Here’s what it looks like:
Now that we know where to find it, let’s check how to use it.
To use it, you need to provide some information, so let’s look at them individually.
The file that we want to parse. Notice that we’re picking the files from the UI, and it displays its path.
But behind the scenes, Power Automate is keeping the file ID. So if you provide a path instead of the ID, it will fail.
Since the Excel file can have multiple tables, we need to pick the one we’re targeting. It’s a good practice to name your tables in your excel file appropriately so that you can easily find them in your dropdown.
Since you’re updating a row, you need to identify the row that we want to edit. To do that, we need to define a “key column” that has a unique identifier for each row. This way, you can tell Power Automate and Excel to kook at column X and find the record Y (more about this below) to update.
Now that everything is configured, we only need to tell Power Automate what row we want to update. Any value is acceptable here as long as it’s unique, although I recommend having sequential numbers or random strings.
Now that we have all the fields configured, Power Automate will display the other columns for us to map:
Notice that, as soon as we select the table, the fields are automatically added. So once you add all the required information, you’ll be able to map the fields and update them.
I see these mistakes happening all the time, so here are some tricks to avoid them.
Only map what you want to change.
Power Automate fetches all the mapped values and sends them to Excel, so any value you map (even if it’s the same) will be updated in Excel. But if you don’t provide value, nothing will be changed.
Since most of the time, we’re fetching data from Excel, parsing it, and updating it back, some people map all fields and change the ones that are being updated, like this:
I would recommend doing the following:
It has two advantages in my opinion:
- It makes it clear what we’re going to update. The second image clarifies that only one field will be updated, and it’s a vast advantage, especially in Excel files with a lot of columns.
- We avoid issues with the update. Every time we fetch and update data to Excel, the data is slightly converted, for example, integer values, percentages, and dates. When we update the information, we’re always risking the data being incorrect because of this conversion back and forward. So updating what we need dramatically reduces this risk.
If you want to make a value “empty,” you need to do this
Not mapping something won’t make the value empty. For example, if you do this:
Only the value in “Column1” will be changed. All the others will not be altered in any way. So if you want to remove the value you need the “null” function to do it.
It works for any field.
Not thinking about data types.
Like we mentioned above, Power Automate fetches the data from Excel and converts it into data types that make sense to Power Automate. If we provide, for example, a string with a date to Excel, we may have issues like incorrect dates or even invalid values.
In the case of the dates, we even have an “advanced property” that allows us to pick the date’s format.
Be very mindful of the format of the fields because if you update with valid data but incorrect data types, you may have errors or, worse, invalid data in your Excel file.
You’ll need that the Excel file has both a “table” and a “key column”. Only with this, you’ll be able to identify the record and update it.
Here are some things to keep in mind.
Keep IDs as strings or numbers.
It’s a lot easier to have identifiers as sequential numbers or random strings since we can reference them without any transformation. However, things become more error-prone if you have dates since dates can be represented either as a string or a number. So keep things as deterministic and straightforward as possible.
Don’t generate the ID for Excel.
I would strongly advise providing calculated IDs. You may have an object and think, “I want to get the next one”, so it’s the ID+1. By coincidence, it could be, but you can’t be sure, so don’t do it. Since the values can be “anything” and the sequence is not controlled, constantly update values that you fetch in previous actions, like the” List rows present in a table” action.
Name it correctly
The name is super important in this case since we’re going to update an Excel file. Always build the word so that other people can understand what you are using without opening the action and checking the details, like what excel file is being updated or why.
Always add a comment.
Adding a comment will also help avoid mistakes. Indicate where the information comes from or why you’re only updating field A or B, for example. It’s essential to enable faster debugging when something goes wrong.
Always deal with errors.
Have your Flow fail graciously and notify someone that something failed. It’s horrible to have failing Flows in Power Automate since they may go unlooked-for a while or generate even worse errors. I have a template, and a template that you can use that will help you make your Flow resistant to issues. You can check all details here.
Back to the Power Automate Action Reference.