How to get selected Excel row(s) in a Power Automate flow

“Should I use ‘Get a row’ or ‘List rows present in a table’ to get a specific row from Excel in my Power Automate flow? I can’t get neither of them to work!”


Power Automate gives you two options how to extract data from an Excel file. You can get a single row with the ‘Get a row’ action, or you can get more of them using the ‘List rows present in a table’. But each of the actions has its limitations. While the ‘List rows…’ looks similar to the SharePoint ‘Get items’ action, ‘Get a row’ filter looks a bit different. It has ‘Key Column’ and ‘Key Value’ fields, which allow only one filter and might be a bit confusing. Similarly, the ‘List rows…’ action won’t allow you to use multiple filters either, even though it can return multiple rows. How do you then get the desired row(s) using these actions?

Note: the data in your Excel file must be in a table, otherwise you can’t process it with Power Automate. If you need to add the table I recommend Damien’s article.

Get a single row using a single filter

As already mentioned, if you’re looking for a single row, you can use the ‘Get a row’ action. Instead of the Filter Query it has the ‘Key Column’ and ‘Key Value’ fields. But it is a Filter Query, only looking a bit different. You can imagine the ‘Key Column’ as the left side of the filter, and the ‘Key Value’ as the right side. The operator is always ‘eq’ (is equal to).

Column eq 'Value'

Key Column = Column
Key Value = Value

As you can see in the example, if you work with ‘Get a row’, the column name can contain spaces. This is something the ‘List rows…’ action won’t accept. On the other side, if there isn’t any row that fits the filter, the action will fail!

Get multiple rows using a single filter

If you want to get multiple rows, you must use the ‘List rows present in a table’ action. This action won’t only return multiple rows, but you can also use more complex filters than just ‘eq’. The supported filters are ‘eq’, ‘ne’, ‘contains’, ‘startswith’ and ‘endswith’.

Unfortunately, this Filter Query supports only a single filter. It’s fine if you want to filter by a single column, e.g. to skip empty rows or import only some tasks to Planner. It won’t be enough if there’re multiple filters involved.

Also, as mentioned before, you can’t use column names with spaces in the Filter Query. All columns must have a one-word title. But it won’t fail if there’re no rows fitting the condition.

Get one or more rows using multiple filters

Since the ‘List rows…’ action supports only one filter, you must add the other filters later. That means adding the ‘Filter array’ action to filter the rows further. The main filter in the ‘List rows…’, the others in the ‘Filter array’. It can be a single filter, or multiple filters, as many as you need.

It’s the same approach in both situations when you need multiple filters, no matter if it’s only ore or more rows.

Just don’t forget that you must work with the output from the ‘Filter array’ later (which you can access manually or by adding a ‘Parse JSON’ action).

Summary

When you use Power Automate to process Excel files, you must think about the rows you want to get. Is it a single row and the column has always value? Use the ‘Get a row’ action. Is it a single row but the column doesn’t have always value, or you need multiple filters? Use the ‘List rows…’ and potentially the ‘Filter array’ actions. And the same applies if you need multiple rows or multiple filters.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

Add a Comment

Your email address will not be published. Required fields are marked *