Power Automate: Office 365 Excel – Get a Row action

Although Excel is known for being a standalone application, there’s a lot that we can do with Power Automate and tasks that we can automate. Today we’ll explore getting a row in an Excel file using the “Get a row” action. This action works for files in SharePoint, Teams, or OneDrive for Business so let’s look at how to use it.

Where to find it?

You can find it under “Standard”:

Pick “Excel Online”:

Select “Get a Row” 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 “Get a row” 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 important to define the one you want to manipulate the data. 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 a file, we need to tell Power Automate how to identify the row we want to get. Since there could be multiple tables in Excel, Microsoft required a “Key Column” that identifies the row we want to get inside the defined table. If you don’t have one, you can use the “Add a key column to a table” action that will do it for you.

Since we provided the path before, Power Automate checks the file and gives us a list of the columns in the provided table.

Please be aware that if you dynamically define the path, table, or file, you need to provide the “Key Column” name since Power Automate won’t find the list of columns before the Flow runs, so it can’t present you with a list.

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

Key Value

Finally, we need a key value that identifies the row we want to get from the table. Notice that we’re getting the item by providing an identifier, not the row number, since the same row can be inside multiple tables. The key value needs to be unique within the “Key Column” and should be a number.

Usage

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

Let’s say you want the information for the employee with ID “2”. For our example, we’ll use OneDrive for Business, so here’s what it looks like:

After we run, we get the following information:

Be careful to ensure that the ID that you’re picking exists in the table otherwise, you’ll get an error. For example, if we don’t have the ID “3” in the table, we’ll get the following exception:

No row was found with Id '3'.

You can find the fields in the “Dynamic Fields” tab if you want to access them. For example, let’s use a compose action:

Please note that you won’t be able to do this if you use dynamic fields for any of the elements, like the path, for example. Since Power Automate can only know the structure of the file while it’s running, it’s not possible to add the fields as a preview to pick them in the UI.

Finally, notice that the values returned are not formatted and, in some cases, don’t have the format you would expect. For example, dates are returned as numbers, so if you want to convert the Excel number dates into “real” dates, here’s an article on how to do it. Or if you need to check this article to convert the dates up until the second. There’s also an article that shows you how to format the date and show information in other languages.

Please also consider that numbers don’t contain formatting like currency or custom Excel formats that you define. If you want to configure the numbers in other formats, you need to use the “Format Number” action after you get the data.

Limitations

At this point should not be considered a limitation anymore; the “Get a row” 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 what we’re trying to get. For example, if you’re trying to get information about an employee, mention that in the name of the action. 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 the reason for getting the Excel file row. 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 Value” doesn’t exist in the “Key Column,” the action will return an error, so it’s essential to have a parallel branch that catches these cases.

Back to the Power Automate Action Reference.

Photo by Becca Tapert on Unsplash