Filtering lines of Excel file to send mail to names in selected lines

[EngineeringLion]

Hi all, I try to develop a workflow to filter lines of an excel table and send emails to people mentioned in the selected lines. My problem currently is that I can not find a useful activity to select multiple lines from excel and define filter criteria. Has anyone a good hint or can help with that problem?

"Get a line" only finds the first line that fits the criteria and then stops. In a file with 1000 lines I want to filter out some 100 or 200 by criteria in different columns. Colum X must be equal to the letter "n" and column Y must be bigger than 0.

[DAllen365]

@EngineeringLion I believe you are looking for the "List rows present in a table" Excel action:

You can read the MS documentation for it as it does have some limitations of the filter query you can use, specifically:

• Supports the following filter functions: eq, ne, contains, startswith, endswith.
• Only 1 filter function can be applied on a column.
• Only 1 column can be used for sorting.

Note that the filter does not support greater than or less than unfortunately.  If this a deal breaker, you could try to get the data into an alternative format like a SharePoint list or something, or just read from Excel all of the rows, and filter in Power Automate using like select and union. 

----------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

[v-LilyW-msft · Accepted solution]

Hi @EngineeringLion 

You can use Filter array action and use advanced mode of Filter array to integrate the two conditions.

Expression: @and(equals(item()?['X'], 'n'),greater(item()?['Y'], '0'))

This is my test excel:

After flow runs, the column with ID equal to 123 will be filtered out.

Hope the content above may help you.

Best Regards

If my solution helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EngineeringLion]

Thank you, that works, but then I need to create another column in the excel file to use filters for both values ( IF(And...))

[EngineeringLion]

Thank you, I could continue with this solution.

Only thing is that it didn't filter for greater than 0 correctly with this formula. After endless testing I found that I have to define "Y" as integer and 0 without ' '.

So in the end my fomula looks like:

@And(equals(item()?['Date of Completion'], 'n'),greater(int(item()?['Overdue time']), 1))

[EngineeringLion]

Hello v-Lily, I work with this workflow and its fine. Only Problem that I have is that a reference date from the excel file is shown as "43001" in the automated email instead of 19.10.2019.

How can I change format in the email?

In the "List rows" order, the ISO 8601 format is defined, also in excel the cell is a date format. (here the "Deadline" is the required date)

[PaulHet]

Hi, I have a similar problem... I manage to filter the array successfully, but when it comes to actually send the emails, it keeps ignoring the filter and sending like 100 emails.  Because the email address is listed in the original 'List rows in table' it keeps going back to that. How do I send an email based only off the information in my filtered array?