how to create a flow to select multiple row values based on a matching criteria of a column value

[ApexFire]

Hi,

I have  a below sample table,

E	M
a	1@xyz.com
b	1@xyz.com
c	1@xyz.com
d	2@xyz.com
e	2@xyz.com
f	3@xyz.com
g	3@xyz.com
h	4@xyz.com
i	5@xyz.com

I require to send mail using flow based on following criteria

send mail to 1@xyz.com with data as {a,b,c)

send mail to 2@xyz.com with data as {d,e)

send mail to 3@xyz.com with data as {f,g)

send mail to 4@xyz.com with data as {h)

send mail to 5@xyz.com with data as {i)

Please help me to achieve this using the 'Power Automate Flow'

Thanks in Advance

[v-xiaochen-msft]

Hi @ApexFire ,

I made a sample for you:

Create a list in sharepoint. “M” is a text column and contents are Emails.

Create a flow.

Create an empty array.

Get items in the list.

Select column "M" and use union() to deduplicate.

union(body('Select'),body('Select'))

Filter items in “Apply to each”.

items('Apply_to_each')

Appends the “Title” of the filtered item to the array.

items('Apply_to_each_2')?['Title']

Convert the array to the desired string format.

join(variables('varA'),',')

Send an email.

Empty the array.

Save and run flow.

Best Regards,

Wearsky

[ApexFire]

Hi Wearsky,

Thank you for helping me out by sharing the Flow to get an idea.

I tried to replicate it using my data, the Flow is succeeding but I am not getting any output.

It seems last subFlows under 'Apply to Each' are not executing. Attaching the result of Flow snippet for your  ready reference. Please advise what I am missing out.

FLOW SNIPPET

Flow not Working

Addional Info:  In my actual Excel file, I have 10 columns and the last column is analogous with column M*  which you have used in your sample table and I am trying to fetch the 2nd Column values (of my actual file) as desired output values.

Thank you.

[grantjenkins · Accepted solution]

I've got a similar solution but avoids the second inner loop.

Below is the list I've used for this example.

Get items retrieve the items from the list.

Select extracts the email address into a simple array. Note that for Map I use Text mode (see arrow on screenshot).

Compose Email removes duplicates using the union expression.

union(body('Select'), body('Select'))

Apply to each iterates over each of the unique email addresses.

Filter array will return an array of items from Get items where the email is equal to the email of the current item in the loop.

Select Values gets the values in our Title column from out the output of Filter array using the following expression. Note that I'm using Text mode for our Map as we did for the emails (see arrow on screenshot).

item()?['Title']

And finally, we use Send an email using the Current item (the current email address) in our To field, and the following expression to join our values from Select Values, separated by a comma and space.

join(body('Select_Values'), ', ')

An example of one of the emails after running the flow.

[grantjenkins]

@ApexFire Just read your reply and saw you are using an Excel file. Is your data inside an Excel Table (formatted as a Table)? And if so, what would the headers be for the two columns you are trying to retrieve?

[ApexFire]

Thank you very much @grantjenkins , Your solution worked completely for me.

[ApexFire]

@grantjenkins , Yes my original data is in Excel file (already is formatted as a Table), which I have uploaded as  a sharepoint list for testing the solution. can we directly use the Table formatted Excel file without uploading as a sharepoint list?

[grantjenkins]

@ApexFire Sorry for the late reply. If your Excel file is stored in a SharePoint library, then you can just replace Get items with List rows present in a table to get the data from the Excel Table rather than a SharePoint list.

[ApexFire]

@grantjenkins , No issues and thanks for the reply. Acutally, I had already tried experimenting the same solution which you have now advised and it worked like charm.