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:

Pro Tip:
Power Automate tends to save the most common actions in the main screen, so check there before going trough the full hierarchy. Also you can use the search to quickly find it.

Now that we know where to find it, let’s check how to use it.

The fields

To use it, you need to provide some information, so let’s look at them individually.

Location

Microsoft did a fantastic job uniforming the locations in one place. In the same dropdown, you can find Excel files in your OneDrive, SharePoint sites, and Microsoft Teams, for example.

Document Library

Although document libraries usually associate with SharePoint, we’re using them behind the scenes in OneDrive and Microsoft Teams.

Even OneDrive has a default document library since SharePoint does all the work behind the scenes, so you’ll always have at least one.

File

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.

Table

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.

Key Column

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.

Key value

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.

Usage

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.

Common mistakes

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:

  1. 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.
  2. 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.

Limitations

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.

Recommendations

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.

Am I missing something? Leave a comment or interact on Twitter. Let’s work together to make this reference as complete as we can

Back to the Power Automate Action Reference.

Photo by Markus Winkler on Unsplash

Manuel Gomes

I'm a Project Manager with experience in large projects and companies. I've worked in the past for companies like Bayer, Sybase (now SAP) and I'm currently working for Pestana Hotel Group.

View all posts by Manuel Gomes →

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: