This request comes up a few times each week where folks have a list of tasks or items assigned to a user (or some other field), and they want to send a regular summary of open things to each respective user/field. Typically, we start building the thing and end up with a single eMail being sent for each item which is less-than-ideal. If you've bumped into this, chances are you have your trigger, then an action to get your list of items, and then you have a Send eMail step (or some other action) inside an Apply to Each loop.
The List
For my example data source, I am using a SharePoint list but this technique could really be applied to anything, as long as the results coming back are in an array.
For the first demo, we want to send a regular summary to each person in the eMail list that would only include their items. Lisa should get an email with boat and scooter, while Bob would get plane, ship, and motorbike.
The Build
For my trigger, I am simply using a manual trigger followed by the SharePoint "Get Items" to get the above list.
After that, we will want to narrow down to just a list of the unique email addresses.
To do this, follow the Get Items with a "Select" action and in the From part, put the 'value' from the "Get Items", and then in the map, put "eMail" (without the quotes) and where it says "Enter value" add the dynamic value from the SharePoint list for the eMail addresses (Special note here that there are some field names that SharePoint uses inherently behind-the-scenes, naming a column 'Email' might be problematic later, but worked ok here...maybe because of my casing "eMail").
This will produce a list with as many rows as you have items, but will only be the email address column. To get just the unique email addresses, add a "Compose" action and use the expression builder with the Union() function to self-reference...this will produce a list of 3 email addresses in our example. Here's my expression (I renamed my Select to "Get Mails" and the Compose action to "UniqueEmail").
union(body('Get_eMails'),body('Get_eMails')) 
Next, we want to cycle through each of those unique email addresses to use them as a filter on your Get Items results. Add an "Apply to Each" loop, and put the output of the above Compose in the "Select an Output" box.
Inside the apply to each loop now, we will add a "Filter Array" action and put the Value from your "Get Items" in the From box.
For the criteria, put the dynamic value "eMail" from your "Get Items" on the left, select "is equal to" and then this expression in the right:
items('Apply_to_each')?['eMail'] 
This says 'get me all of the rows that match whatever email from our list of unique emails, we are focusing on right now'. The Apply to each will cycle through each one.
Next, I added a step to "Create an HTML Table" from the body of the Filter Array.
And finally, you can use a "Send eMail" using items('Apply_to_each')?['eMail'] in the "To:" bit, and then the HTML table output as the body.
For added sparkle, check out April Dunnam's article,
Formatting HTML Tables in FlowThe Curveball: Grouping on a Lookup Field in SharePoint
Sometimes, I get so enthusiastic about quickly solving an issue that I miss something important in the scope. With this one, I had missed that the grouping column was a lookup from another list..but just a few minor adjustments and we can pivot. Here's the same list with the added column:
This adds another layer for us to dig when we get our list of items and create our unique list as we can see from the output below:
Making the Changes
The first step we want to adjust is our "Select" action up at the top so that we are grabbing the values from the owner column. If we hover over the old field, we can see the expression behind it and adjust accordingly:
By getting rid of the eMail dynamic value, and replacing it with our own expression, we can grab the email addresses of the Owners:
item()?['Owner']?['Value']
Since everything else is referencing previous steps, we only need to use the same expression on the left side of the "Filter Array" action to complete the pivot.
This will result in two separate tables being sent because our list only had two owners.
I liked this one because it comes up quite a bit and there are plenty of different nuances that might affect how the problem is approached.
If you'd like to see how a Business User and a Developer collaborate on Power Platform solutions, be sure to check out Ashley Rogers' new channel on YouTube. Here's our introduction to the series, Everyday Automation:
If you're just getting started in Microsoft Power Automate and are looking for a quick guided-learning session, find my Micro-Job on Collab365. We can chat about what is getting in the way of your flow, and I can point you in the right direction.
