Have you ever thought about structuring data in MS Flow like grouping? In other tools this operation is very straight forward, just as far as a simple click of a button. In one of my current projects I faced this common scenario to be handled within MS Flow and I’d like to share this with you.

Let’s imagine the following

  • you have a table with multiple hour registrations for several projects
  • each project has a project manager
  • you’d like to send a list of consumed hours to the individual project managers using MS Flow

As mentioned above, this task is very common and easy to do in tools like PowerBI or Excel. To illustrate what we’d like to achieve, I did the same in MS Excel using a Pivot Table and copied a filtered view to MS Outlook.

Grouping Line Items – Scenario in Excel and Outlook

Let’s get started by just creating a blank flow.

Adding the Input Table

The first task now would be to add your input table to flow – in my example this is MS Dynamics Finance and Operations with entity “Journal Trans” which holds all desired project transactions.

List Hour Journal Lines of D365 Finance and Operations

Building the Project List – Part 1: Empty Array

Next thing to be added is an empty array where we’d like to write the unique project IDs to.

  • Add an “Initialize Variable” action
  • Give it a name – I named it “UniqueProjects”
  • Set the type to array
  • Set the start value to “[]”
Initialize Array Variable “UniqueProjects”

Building the Project List – Part 2: Process All Line Items

Now as we have all prerequisites ready we can start to process the line items – one by one – to figure out which project IDs we have in the list. What we now need is a “Apply to each loop” to call each line item. The input for this action now needs to be your input table – in my case the list of items I called from D365 Finance and Operations.

Process Each Line Item Using “Apply to each loop”

Building the Project List – Part 3: Find Unique Items

Next thing to achieve is to find unique values in the list and write them to our array variable. MS Flow doesn’t come with a proper grouping feature but comes with a neat function which

  1. allows us to union values
  2. Skipps all values which are already in the list

To use this function we now add a compose action (1) and set the value using an expression.

union(variables('UniqueProjects'),createArray(item()?['ProjectId']))

The expression one by one…

Function/ValueDescription
union combines multiple uniqe values
variables(‘UniqueProjects’)First thing to combine is our array variable
createArray(item()?[ProjectId’]Second thing to combine is the project ID of the actual processed item which we put in the format of an array in order to fit to our array variable
Union The Project IDs And Write Them to Array Variable

As a second action we use a “set variable” action (2). We’ll now set the variable “UniqueProjects” to the output of our union action. With each loop we’ll now

  • Union the content of our array with the next project ID which is not yet part of the array
  • Write the complete union back to our array

Check the results

We can now fire MS Flow to produce the first results.

First Result – A List of Unique Project IDs

Sending Emails: Part 1 – Process All Unique Projects

So far it’s been very easy. Let’s go ahead and build the list of line items for each project. As we already now, we’ll need an “Apply to each loop” to process all unique projects coming off our array variable.

Filter the Input Table By Processed Unique Project Id

One by one

ActionFieldValue
Apply to eachInputOur array variable “Unique Projects”
Filter ArrayInputOur Input List, List of Transactions
Filter ArrayFilter ColumnField ProjectId coming from the input list
Filter ArrayComare ValueThe current cylce item

Now as we have the desired items in place, we may put them in an HTML table which will also be the format we’ll use to dispatch the lines by email.

Create HTML Table in MS Flow with Customized Columns

Let’s see what we have here

ActionFieldValue
Create HTML TableFromThe body/outcome of the previous filter action
Create HTML TableInclude HeadersYes, as we’d like to have a header row in the table
Create HTML TableColumnsCustom, as I’d like to define the columns which are included in the table
Create HTML TableHeaderThe individual header name for each column
Create HTML TableValueThe value to be included in the individual row.
It’s just “item()?[‘FIELDNAME’]”

Seems we have everything ready to dispatch the emails containing the individual project hours for each project in MS Flow. In real life we’d also have to find the recipient somewhere in a table where project and project manager are married, but for now I assume we already have this information in place.

Sending Emails: Part 2 – Build Email

The last step will not bring too much suprises. In general we’ll create an “Outlook – Send an email” Action.

Prepare the “Send an Email” Action in MS Flow

I’ve set the values as follows – as always feel free to adapt according to you requirements.

ActionFieldValue
Send an emailToThe recipient. I set this to a fixed value for now – in real life you’d place your picked project manager email address here.
Send an emailSubjectSome fix text + the actual processed item (project ID), Expression: items(‘Apply_to_each’)
Send an emailBodyThe email text. I’ve put some fixed text here. The added output will place the HTML Table in here which we produced in the last step. Expression: body(‘Create_HTML_table’)
Send an emailImportanceWhen it comes to send emails using the outlook send an email action, it’s always good to set this value. If you don’t – Flow defaults to low priority.
Send an emailIs HTMLAs I used some linebreaks in the body and we are going to send an HTML table, we have to set this value to “yes”.

We are now finally ready to fire the flow 🙂 Let’s see the output!

Result – Grouped Items in Mails

Here we are. Of course there are some things we could do better, like rounding hours or formatting the date but most important – we managed to split the whole table by project ID.