How to get the distinct values of sharepoint coloumn in power automate and perform ApplytoEach Action On the Output

[Govind_Prajapat]

Hi 

I have a task list. It has four columns Task, Deadline, ActionOwner1, ActionOwner2. The list is having multiple tasks for single Action Owner. I am not maintaining the list of Action Owners Seperately.  We have to Retrieve the list of ActionOwners from task list itself.

Now I want to Send the Mail to Action Owners 5 days prior to the deadline with the list of all of their subtasks. Keeping in mind that each ActionOwner should get a single mail with his/her subtasks list no matter he/she is ActionOwner1 or ActionOwner2 for a task.

Note: I have tried the Select Action to retrieve the list of ActionOwners but I am not able to Perform ApplyToEach Action on the output of Select Action.

Kindly help with this. How Can I achieve this

[Koen5]

Dear G,

Finding unique/distinct values is a common problem and usually solved with the union() expression. 

Once you know this, you should be able to find many examples of using this expression in the way you like. Check YouTube/ChatGPT/this forum, etc.

Happy flowing,

Koen

[Govind_Prajapat]

that is not working for me I am not able to retrieve the value of action owner from the output of action owners. 

Anybody can please help with this with example

[Matthy79]

Please provide sample data and desired result.

[Govind_Prajapat]

Assume the List of tasks:

Task	Deadline	ActionOwner1(PersonType)	ActionOwner2(PersonType)
Task1	20/11/2023	person1	person2
Task2	15/11/2023	person2	person1
task3	20/11/2023	person3	person2
task4	20/11/2023	person2	person1
task5	15/11/2023	person3	person1
task6	15/11/2023	person1	person2
task7	20/11/2023	person2	person3
task8	15/11/2023	person3	person2
task9	15/11/2023	person1	person3

Now the list for notification configuration settings:

DaysBeforeDeadline	EmailBody
5	5 days to the deadline of following tasks
10	10 days to the deadline of following tasks

Now suppose the Flow is running on 10/11/2023 the mails for the task actionowners should be like below:

1. For Person1:

Hi Person1,

5 Days to the Deadline of following tasks. Please Complete Before the Deadline.

Task	Deadline
task2	15/11/2023
task5	15/11/2023
task6	15/11/2023
task9	15/11/2023

2. For Person2:

Hi Person2,

5 Days to the Deadline of following tasks. Please Complete Before the Deadline.

Task	Deadline
task2	15/11/2023
task6	15/11/2023
task8	15/11/2023

3. for Person3:

Hi Person3,

5 Days to the Deadline of following tasks. Please Complete Before the Deadline.

Task	Deadline
task5	15/11/2023
task8	15/11/2023
task9	15/11/2023

Now Person1, Person2, and Person3 should also get another mail with the list of the tasks the have the deadline after 10 days.

Person1: list - task1 , task4

Person2: list - task1, task3, task7

person3: list - task3, task7

For More Clearance :

Flow Run Date: 15/11/2023

 all action owners should get the list of their tasks which are going to end on 20/11/2023 with the email body 5 days to the deadline

Flow run date: 05/11/2023.

all action owners should get the list of their tasks which are going to end on 15/11/2023 with the email body 10 days to the deadline.

@Matthy79 I believe above Justification has given the clear-cut idea of my requirement. I have already tried suggested methods by @Koen5 but these are not working in my case.

So, Please Provide the concise solution for the above requirement. Kindly give a step by step solution if possible. I am tired of trying different methods.

Thanks in advance.

[Matthy79 · Accepted solution]

This is how it could work.

Depending on the real data it would be better to create it a little different.

{"id":"f36864c1-ea54-48db-b325-0d30656495cf","brandColor":"#8C3900","connectionReferences":{},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Scope_Notify","operationDefinition":{"type":"Scope","actions":{"Deadline_Data":{"type":"Compose","inputs":[{"Task":"Task1","Deadline":"2023-09-19","ActionOwner1":"person1","ActionOwner2":"person2"},{"Task":"Task2","Deadline":"2023-09-14","ActionOwner1":"person2","ActionOwner2":"person1"},{"Task":"Task3","Deadline":"2023-09-19","ActionOwner1":"person3","ActionOwner2":"person2"},{"Task":"Task4","Deadline":"2023-09-19","ActionOwner1":"person2","ActionOwner2":"person1"},{"Task":"Task5","Deadline":"2023-09-14","ActionOwner1":"person3","ActionOwner2":"person1"},{"Task":"Task6","Deadline":"2023-09-14","ActionOwner1":"person1","ActionOwner2":"person2,"},{"Task":"Task7","Deadline":"2023-09-19","ActionOwner1":"person2","ActionOwner2":"person3"},{"Task":"Task8","Deadline":"2023-09-14","ActionOwner1":"person3","ActionOwner2":"person2"},{"Task":"Task9","Deadline":"2023-09-14","ActionOwner1":"person1","ActionOwner2":"person3"}],"runAfter":{}},"DaysBeforeDeadlineWarning_Array":{"type":"Compose","inputs":[5,10],"runAfter":{"Deadline_Data":["Succeeded"]}},"Apply_to_each_DayWarning":{"type":"Foreach","foreach":"@outputs('DaysBeforeDeadlineWarning_Array')","actions":{"Filter_Tasks":{"type":"Query","inputs":{"from":"@outputs('Deadline_Data')","where":"@equals(item()['Deadline'], addDays(utcNow(), items('Apply_to_each_DayWarning'), 'yyyy-MM-dd'))"},"runAfter":{}},"Select_ActionOwner1":{"type":"Select","inputs":{"from":"@body('Filter_Tasks')","select":"@item()['ActionOwner1']"},"runAfter":{"Filter_Tasks":["Succeeded"]}},"Select_ActionOwner2":{"type":"Select","inputs":{"from":"@body('Filter_Tasks')","select":"@item()['ActionOwner2']"},"runAfter":{"Select_ActionOwner1":["Succeeded"]}},"Apply_to_each_ActionOwner":{"type":"Foreach","foreach":"@union(body('Select_ActionOwner1'),body('Select_ActionOwner2'))","actions":{"Filter_Tasks_for_ActionOwner":{"type":"Query","inputs":{"from":"@body('Filter_Tasks')","where":"@equals(or(equals(item()['ActionOwner1'], items('Apply_to_each_ActionOwner')), equals(item()['ActionOwner2'], items('Apply_to_each_ActionOwner'))), true)"},"runAfter":{}},"Create_HTML_table_for_ActionOwner":{"type":"Table","inputs":{"from":"@body('Filter_Tasks_for_ActionOwner')","format":"HTML","columns":[{"header":"Task","value":"@item()['Task']"},{"header":"Deadline","value":"@item()['Deadline']"}]},"runAfter":{"Filter_Tasks_for_ActionOwner":["Succeeded"]}},"Compose_Dummymail":{"type":"Compose","inputs":"Send Mail with this information to @{items('Apply_to_each_ActionOwner')}:\n\n@{items('Apply_to_each_DayWarning')} Days to the Deadline of following tasks. Please Complete Before the Deadline.\n\n@{body('Create_HTML_table_for_ActionOwner')}","runAfter":{"Create_HTML_table_for_ActionOwner":["Succeeded"]}}},"runAfter":{"Select_ActionOwner2":["Succeeded"]}}},"runAfter":{"DaysBeforeDeadlineWarning_Array":["Succeeded"]}}},"runAfter":{}}}

Just copy everything and put it into a dummy flow.