Power BI > Power Automate > Email - Apply to each

[andy_jay]

Hi,

I am new to power Automate, I'm currently invested 3 hours and a you tube tutorial!

I have data in a power BI report I would like to action.

I have used Power automate to read the data and send an email to customers from this data.

If a customer has multiple rows, they get multiple emails, i.e. 9 rows, 9 emails.

I would like to bunch together the rows so the info is all listed in the body of one email rather than 9 separate emails!

Does anyone know if this is possible?

Here is the current flow:

I have 'apply to each' customer, then built a body of an email inserting the affected consignments and wanted an email to inform customers of their orders.  Currently if 5 orders, 5 emails - 9 orders then 9 emails........I wanted 1 email listing all rows within the body...........Any ideas??

Many thanks in advance 🙂 

[Pstork1]

Yes its possible.  Here's the pattern that you need to implement to do it.

1) Sort the data by customer

2) Initialize two variables.  A string to hold the current customer, initialized to "none", and an array to hold the results.

3) Inside your loop check to see if the current customer is the same as the variable.  If it is then append the results to the array.  If its not then convert the array to an html table and send the email.  then change the customer variable to the current customer, and set the array with that customers results.  Repeat the loop.

4) When you exit the loop convert the array one more time to an HTML table and send the last email.

3) 

[andy_jay]

Thank you for your input, I've only just come back to this project but I have some specific questions that unfortunately tutorials are not helping with.

1) Sort Data by customer - Below are the values I have available in my dataset, in rows by product, some customers have 9 relevant rows others just 1.  This is where I need Power automate to send 1 email detailing the 9 (or however many relevant to account) rows/records.

2) A string to hold the current customer, initialized to "none":

 .....and an array to hold the results, not sure what to enter as the value in this instance?

3) Inside your loop check to see if the current customer is the same as the variable.  If it is then append the results to the array.  If its not then convert the array to an html table and send the email.  then change the customer variable to the current customer, and set the array with that customers results.  Repeat the loop.

At this stage, my current understanding of what is required here is not great, my desire to understand is burning bright so any help would be greatly appreciated. 

4) When you exit the loop convert the array one more time to an HTML table and send the last email.

(Not sure on the last step here)

In an ideal world, it would be to have a part time support contract with a view to unlocking the bits where lack the current knowhow, is this possible?

[Pstork1]

1) You just need to sort the data you are retrieving by the customer field so all of one customer's records will be together when you process them.  How you sort is dependent on how you are retrieving the data.  What action are you using to get the data set.

2) Leave the array blank when you initialize it. it doesn't need a value at that point.

3) This is where you compare the current field value in the loop to the customer name stored in the variable.

4) You process each customer when you get to a loop where the customer name doesn't match the variable. So when you exit the last loop you won't have processed the last customer.  So you need to do it when the loop is completed.

[andy_jay]

So currently I am using Power Automate through Power BI. 

The aim is that all the data relates to delayed shipments. 

I have used the Power Automate visual to set up a flow whereby it emails the customer using the data fields I selected.

The data fields contain customer column amongst others and this is where I need to group by.

Body of email created using fields and relevant delayed product, currently if 9 products they get 9 emails, i want the body of email to list all 9 products, listed below is current body data, it essentially lists one product but here a I want to list all 9

There is already an apply to each on the send email and that is based on the customer column/field.

Thank you for your input, I feel so close but yet so far 😄 

[Pstork1 · Accepted solution]

There is no GroubBy capability in Power Automate.  You have to do it manually.

1) Make sure the data is sorted by customer in Power BI before you send it to Power Automate.

2) Inside the loop you have already do a condition to see if the customer is the same as the customer stored in the variable.

    - If the customer is the same then add the four fields you want in the email to an array variable

   - If the customer is different and not blank then do the following

        a) convert the array variable to a HTML table

        b) Send the HTML table to the customer stored in the variable in an email

        c) store the new customer in the variable

        d) clear the array variable and add the four fields to the array

3) When you exit the loop the last customers information will still need to be emailed so do the following

        a) Add the four fields to the array

        a) convert the array variable to a HTML table

        b) Send the HTML table to the customer stored in the variable in an email

[andy_jay · Accepted solution]

Thank you so much for your help, as a power automate newbie I'm pretty impressed with the results.

I now understand the hatred for Apply to each! 😄

I merged your advice with a You Tube tutorial and used Excel instead of Power BI, the results are exactly how I envisaged.

The icing on the cake would be to reference in the body of the email, columns from the spreadsheet so we can personalise the text and make it more customer specific with names etc - Currently apply to each is hindering this by throwing the flow out of sync and sending multiple emails referencing the same 🙂