Power Automate: Office 365 Excel – Add a key column to a table action

If you do operations in Excel using Power Automate, you know that you need to use a “Key Column” and a “Key Value” pair to identify the item you want to operate on. For example, if you use the “Delete a row” action, you need to provide it so that Power Automate knows what element to delete. But what about if you don’t have a “Key column”? You can create one yourself directly in the Excel file, but Power Automate also has the “Add a key column to a table” action that can do that for us automatically.

In case you don’t know what “Key Columns” are, I have an article here that explains them in a lot of detail.

Let’s look at how to use it.

Where to find it?

You can find it in “Standard”.

Select “Excel Online”:

Pick the “Add a key column to a table” action:

Here’s what it looks like.

Now that we know how to find it let’s understand how to use it.

Fields

You can find some fields to configure before using the “Add a key column to a table” action, so let’s look at them individually.

Location

As mentioned before, we can use SharePoint, Teams , or OneDrive For Business to find our Excel files. To do it, select the location where you want to find the data. For example, for OneDrive For Business:

As you see above, you have a lot of “Groups” sites that are commonly Microsoft Teams sites.

If you’re looking for SharePoint sites, they are commonly displayed like this:

Since they all behave the same way, I’ll refer only to SharePoint sites from now on, but keep in mind that you can also use Microsoft Teams and OneDrive For Business. I’ll highlight the differences when appropriate.

Document Library

Since SharePoint sites can have multiple document libraries, we need to pick them from the list.

There’s always one created by default called “Documents” or something different depending on your language, but since it’s a mandatory field, you need to pick it; otherwise, you won’t be able to choose the file from the following field.

The same behavior occurs for OneDrive For Business, even if it’s not possible to create document libraries. Microsoft creates a document library that you can pick from the list.

File

Now that we have the locations defined, let’s look at a file. You can pick the file in the “File Browser”.

Or you can provide the path yourself, but I discourage this. Although you can do it, Power Automate sometimes returns strange errors related to the path, especially if the file has special characters. It’s always recommended that you can, of course, select the file directly in Power Automate’s UI.

Table

Power Automate requires the information inside an Excel table to access the data. To find the table, you can go to your excel file, select a row and then check if you have a “Table Design” tab in the ribbon.

If you don’t, then chances are that you need to create a table in Excel before you can continue. Select the table in the dropdown.

The Excel file can have multiple tables, so it’s essential to define the one you want to create the column. Finally, you can provide the table’s name as a “dynamic field,” but to ensure that your Flows always work, I recommend picking it from the dropdown.

Key Column

Now that we have the file adequately configured, we can define the “Key Column” we want to add to the file. Notice that, while ugly, Power Automate suggests a name, but picking the name to use is up to you. I recommend adding something more descriptive that fits more with the naming convention of the other columns, but any name will do.

Usage

Let’s look at a test file with anonymous employee information.

For our example, we’ll use OneDrive for Business, so here’s what it looks like:

Notice that we changed the name of the column we want to create. After we run, let’s look at the file, and we see a new column called “Key Column” created:

Be careful because if you run the “Add a key column to a table” action again, you’ll get an error since the column already exists. Here’s the error if we rerun it:

Column 'Key Column' already exists in the table 

Let’s look at some non-intuitive behaviors.

Non-intuitive behaviors

Since we’re creating a “Key Column”, we would expect that the column would be populated with data, like a unique sequence of numbers we can reference later, but it’s not what happens. Power Automate creates the column and leaves it empty, meaning we’re roughly creating a “normal” column. We need to manually fill the column with unique values we can reference later.

Please note that when you’re creating a column using the “Add a key column to a table” action, you’re only creating a “normal” column, meaning that other columns could exist that can be used as “Key Column” in Flows. A “Key Column” is only a column that contains unique values that can be referenced, and there’s no way to define in Excel that a column is a key column, like when it happens on a database system, for example.

Limitations

Although at this point should not be considered a limitation anymore, the “Add a key column to a table” action will only support Excel Workbook (xlsx) files, so you need to convert your Excel file before you can use the action.

Recommendations

Here are some things to keep in mind.

Name it correctly

In this case, the name is super important to provide the context of where we’re adding the key column, for example. Always build the name so that other people can understand what you are using without opening the action and checking the details.

Always add a comment.

Adding a comment will also help avoid mistakes since we can indicate why the Excel file “Key Column” is added. Also, if you’re adding any elements dynamically, it’s a good practice to explain how the values are obtained. Indicate where the path comes from, for example, if it’s calculated and how. 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 that you can use to help you make your Flow resistant to issues. You can check all details here. Remember that if the “Key Column” already exists in the Excel file, the action will return an error. This could be because you ran a Flow with a “Add a key column to a table” action, for example, so it’s essential to have a parallel branch that catches these cases.

Don’t forget to fill in the column after creating it.

Since Power Automate creates the “Key Column” empty, you need to add the values to the Excel files; otherwise, when you try to use another action that uses the “Key Column”, like the “Delete a row” action, for example, you’ll get an error.

Back to the Power Automate Action Reference.

Photo by Wolfgang Rottmann on Unsplash