How to get selected Excel row(s) in a Power Automate flow
Posted On November 10, 2021
“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’.
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.
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.