Splitting a spreadsheet by email and sending a table

[sukicorn]

Hi

Hoping someone can help me. I have a spreadsheet containing info for separate employees that I want to send to their managers as a tidy table (this bit works fine). I need to be sure that each manager will receive only their direct reports data. My spreadsheet has these columns - Email (which is the manager email), employee name, etc

This is the whole flow:

I think the issue is here somewhere

At the moment it just sends all the data to each email address in the spreadsheet.

Any ideas how to fix please!

[creativeopinion]

@sukicorn Instead of trying to build your full flow and troubleshoot it after. Run tests often and at each section of your flow. This way you can troubleshoot early and have a better understanding of what's going on in your flow.

Make a Copy of your flow and follow along. 

Select Email Addresses Only

Your Select action doesn't require a key. Click on the icon to change the input mode.

Note: The column name with email addresses in my spreadsheet is named Assigned To. Select the appropriate dynamic content for your Select action which would be Email. 

Run a test. Review the outputs. The Select action will output email addresses only. If you review the output of your original flow, your Select action output the dynamic content key long with the email addresses (which isn't necessary).

Unique Email Addresses

Although a Compose action isn't necessary, I recommend it so you can test and review the outputs. Instead of using the union() function in the Apply to Each action, use it in a Compose action so you can review the outputs. Tip: Rename your actions to keep your flow organized.

Insert an expression and use the union() function.

The union() function takes two arrays (separate each by a comma) and combines them while removing any duplicate values.

Click on the Dynamic content tab and insert the output from the Select action above.

Run a test. Review the outputs. 

Apply to Each

Add an Apply to Each action to loop through the array of email addresses. Insert the output from the Compose action above. 

Additionally, add a Compose action inside the Apply to Each action to store the Current Item dynamic content. This way you know which Email Address is being looped through. 

Run a test.

Filter Array 

Add the Filter Array action to filter out the items from your table by the current email address you are looping through. In the From field, insert the value dynamic content from the List Rows Present in a Table action. In the first value field, insert the dynamic content from the column you want to filter on. In your case it's the Email column. Leave the operator as is equal to. In the second value field you can either insert the Current Item dynamic content. I've chosen to insert the output from the Compose action above as it's storing the Current Item dynamic content. Either will work. 

Item Count

Whenever I use a Filter Array action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow. Insert a Compose action. Add an Expression. Use the length() function.

Select the Dynamic content tab and insert the body dynamic content from the Filter Array action into the length() function.

Run a test. Review the output of the Compose action. Tip: Click the Compose action to expand the current email address you are looping through. This way you can confirm that the Filter Array action is filtering out the correct number of rows. 

It's unclear how you want to send the filtered data as you don't have any actions after the Filter Array action in your original flow. 

Take a look at this YT Tutorial: How to Send a SINGLE EMAIL ✉️ with multiple SharePoint list items | Build THIS Power Automate Flow

Although this tutorial covers how to send multiple SharePoint list items in a single email. The concepts covered in this video can apply to your flow as well. 

IN THIS VIDEO:

✅ How to send multiple list items in a single email with a Power Automate Flow

✅ How to create a dynamic date range

✅ How to use the Convert Time Zone action

✅ How to use a Filter Query in the Get Items action

✅ How to count number of items in an array

✅ How to use the Select action to extract a users display name and email address

✅ How to create a unique list of email addresses

✅ How to use the Create HTML Table action

✅ How to customize the HTML Table with CSS styles

✅ How to use the Send an email (V2) action

✅ How to use the Append to String Variable action

✅ How to create a custom list of items for an email

✅ How to use the Send an email (V2) action

✅ How to display singular or plural text based on the number of items returned

Additionally, to learn more about how to use the Filter Array action, please refer to this YT Tutorial: Are you using the Microsoft Power Automate Filter Array Action wrong?

In this video tutorial I’ll show you 3 practical ways to use the Filter Array action and how to use it properly.

1️⃣ Cross-Referencing Data

2️⃣ Filtering by Key

3️⃣ Substring Matching

Did you know that the Condition action has a limit of 10 conditions? Although it might look like the Filter Array action can only accept one condition—this is not true. By using the advanced mode you can enter multiple conditions into a Filter Array action with an expression.

IN THIS VIDEO:

✅ 3 Ways to Use the Filter Array Action

✅ How to use the Scope Action to Group Actions

✅ How to Check the Number of Items returned from a Filter Array Action

✅ How to Cross-Reference Data in Excel with a SharePoint List

✅ How the Filter Array Action Works

✅ How to Access the Dynamic Content from a Filter Array Action

✅ How to Filter Items by a Key

✅ How to Filter Items by Matching a Substring

✅ How to Use Multiple Conditions in a Filter Array Action

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram

[sukicorn]

Thanks so much. I’ve used all those steps and tested that it all works. I need to send an email with a table showing only the data relevant for each email. I have this before the now updated Select function 

this formats the table but I’m not sure it’s in the right spot?

also I need to send the email but I’m not sure how to select each email address and should I use the output from Table ?

[creativeopinion · Accepted solution]

@sukicorn The Create an HTML action is in the wrong spot. You need to have it after you've filtered out the array. As mentioned in my previous post: 

Add an Apply to Each action to loop through the array of email addresses. Insert the output from the Compose action above. 

---

By stopping to test at each section of the flow—you should have a better understanding of the logic of the flow. In this case, while you are looping through each email address, you are using a Filter Array action to filter the items that match the current email address you are looping through. 

You then need to use the Create HTML table action to create the HTML with the data from the Filter Array action. Reference this section of the YT Tutorial I've linked above. I cover how to get the dynamic content from the Filter Array action as you cannot use the dynamic content from the List Rows Present in a table action. 

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram

[sukicorn]

@creativeopinion Thanks so much for your help. The flow is working perfectly!