“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.
Hello, I want to send an email from Outlook on the 10th business day of every month. I am not able to create a scheduled cloud flow that identifies the date and sends the email.
I created a table in an excel file in OneDrive and applied the formula for the 10th Business Day and thought that Flow could pick the date from there and on that day it will send the email, but am not able to do that as well.
Kindly help in creating this flow either using excel to pick the date from there or without excel.
Hello Vikram,
you can’t use trigger condition if you connect to another source that means your flow will have to run every date, check if today’s date is the date in your Excel file in a condition, and then either send an email or end. I think it would work best if you had the dates in the ISO format as a text in the Excel file, and then you can ‘List rows’ and search if there’s a row with today’s date. If there is, send the email. Otherwise do nothing.
Hi. I have a table with 4 columns. The last row of the table is ‘Total Row’ where I am adding sum of the numbers in respective columns (using subtotal function). I want to to paste the entire table in email (including the sum in last row). Using ‘Get row in a table’, ‘Select’, ‘ Create HTML table’ functions I am able to paste all the rows except the last row (where subtotal is being calculated).
What can be the problem?
Hello Mahendra,
the first thought is that the last row is not included in the Excel table.
I am trying to send a massage on Teams once per week using a row from an excel sheet. I struggle with Key value? Can someone guide me here?
Hello Sargol,
Key value is what should the flow search for in the Key column, once it finds the value it’ll take the row and give you the data from that row.