Answered

Filtering an Excel Table by multiple columns

(0) Share

Report

Posted on by kaylee

Hello,

I would like to set up a quarterly recurring follow up using an excel list, and i need it to filter out certain things in 2 different columns. For example, a few clients do not need this follow up email so i need to filter out their business names, (BusinessName column in my table) aswell as if someone has already sent something in (Status column in my table) i dont want to send them another email following up with them. I have tried using "Filter Query" within "List rows present in a table" but i can only figure out how to make it work with one filter. example (BusinessName eq 'Company Name')

Is there a way I can list multiple business names in the filter query along with filtering by the Status column or is there a different way I need to do this?

Thanks

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (2)

v-bofeng-msft on at

Like (1)

Report

Hi @kaylee ,

Is there a way I can list multiple business names in the filter query along with filtering by the Status column or is there a different way I need to do this?

I'm afraid not , it is a known limitation:

The List rows present in table action supports basic filtering and sorting:
- 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.

I think this link will help you a lot:
https://learn.microsoft.com/en-us/connectors/excelonlinebusiness/?WT.mc_id=BA-MVP-5003630#known-issues-and-limitations-with-actions

In addition , you could use the 'filter array' action to achieve your needs.

Best Regards

Bof

Was this reply helpful? Yes No
Verified answer

grantjenkins 11,063 Moderator on at

Like (2)

Report
Since you can only have a single query within List rows present in a table, I would filter on the Status, then use Filter array to continue the filtering on business.

I've used the following Excel Table for this example.

See full flow below. I'll go into each of the actions.

List rows present in a table has the following expression that will exclude any rows where the status is "Completed"
Status ne 'Completed'

Initialize variable creates an Array variable called businessList that contains all the businesses I want to exclude. Not sure where you would have your list of businesses.

Filter array takes in the value from List rows present in a table and has a condition to check if the array of businesses does not contain the current business.

The output in this example would be:
[ { "@odata.etag": "", "ItemInternalId": "e93983f1-fb02-4067-b795-696c0d70cc34", "Business": "Business 001", "Email": "email@business001.com", "Status": "In Progress" }, { "@odata.etag": "", "ItemInternalId": "25c6b5ec-4c8d-4d22-a88d-9ea09d64787b", "Business": "Business 006", "Email": "email@business006.com", "Status": "In Progress" }, { "@odata.etag": "", "ItemInternalId": "0389cdf6-bf7e-4127-bef1-b2a32f328cb5", "Business": "Business 008", "Email": "email@business008.com", "Status": "In Progress" } ]

Apply to each can then iterate over each of the items returned from the Filter array and send an email. The expression used to get the email for each business would be:
items('Apply_to_each')?['Email']

Was this reply helpful? Yes No
kaylee 20 on at

Like (0)

Report

Thank you SO much!! This is exactly what I needed, and it worked perfectly!

Was this reply helpful? Yes No
kaylee 20 on at

Like (0)

Report

@grantjenkins if i wanted to take it one step further and have the flow update the Status column to "Completed" for the people it emailed during the flow, how would I do that?

Was this reply helpful? Yes No
Verified answer

grantjenkins 11,063 Moderator on at

Like (2)

Report
You can add an Update a row action within the Apply to each, so it updates each of the items. Note that this will require you to have a column in your Excel Table that contains unique values (key to uniquely identify each row).

Using the example from before, I've added an Update a row action using the Business column as the unique identifier. The expression used to get the business name for each row is:
items('Apply_to_each')?['Business']

Was this reply helpful? Yes No
chosen67 90 on at

Like (0)

Report

@grantjenkins Super thnx for your extended explanation!

I have a similar flow but need to send multiple attachments (1 per email address). This is done based on an Attachment name in the Excel file. Suggestions on how this step should look?

Was this reply helpful? Yes No