Automatically email reminders from Excel with Power Automate

[milkdesk]

I built a Power Automate flow using this tutorial: https://www.myonlinetraininghub.com/automatically-email-reminders-from-excel-with-power-automate 

It is a super helpful tutorial. Though I am running into a slight problem when trying to add a name field to the email from the excel table. I can get the HTLM table and email (as Current item) fields to work fine. But when I go to add an additional field of "Customer Name" to the body of the email, it suddenly creates an additional Apply to each inside of the apply to each undoing to workflow.

Here is the images of the Power Automate flow without the name field which what I want it to, below is a image of the flow with the name field that undoes the what the above flow is trying to do.

Any help would be greatly appreciated.

[MiDer · Accepted solution]

@milkdesk 

Let`s get through it one by one.

• You start with an array of Excel Rows
  (as it is an array, referencing it would results in an apply to each )
• then select one property of this array to have distinct values on the e-mail addresses within the compose action with the union formula
  (still an array, but only unique values, need this to send only one e-mail for all my associated rows)
• Afterwards you loop over all previously gathered unique e-mail addresses
  • as first action you filter the array of Excel rows on the current e-mail address given within the Apply to each iteration.
    Selecting the related Excel Row rows is done with the item()[ColumnName].
    
    In case this here would be my Excel table:
    

    the first apply to each step creates this HTML table

    

    the second step this one here

    

At this point you have one array as result of the filter array action (even though potentially your Excel file contains only one row for a certain e-mail address.
The other array is the unfiltered result of the List Rows present in a table action from the very beginning

Regardless which one you would select, the logic always presumes multiple potential items, thus adding an apply to each.

In case I would now want to have another property within my e-mail, the desired information is within the output of the Filter array within the Apply to each step already.
For me it`s the column ReminderDate, for you the CustomerName.
It might be that all filtered results show the same value for the column, but it couldn`t as well. 

You could now do it exactly the same way as already done with the e-mail addresses.
Select the CustomerName from the output of the Filter array

Get unique values with union().
If you put it in another compose first and add its result to the e-mail or if you define it directly within the body of the mail is up to you. In case you want to review the Flow at a later point in time, it could be beneficial to have it broken down in more pieces.

Overview of the apply to each

Results of the two runs (as I have only two unique e-mail addresses in my table

1

2

 
I hope this helps you a bit further.

[milkdesk]

@MiDer that works great. I appreciate your help with this. Adding an additional Select and Compose with a union expression does the trick.

The next hiccup is the Compose Output into the email body is now inserting the following ["CompanyName"].

Do I use a replace expression to remove both [" and "] or is there an easier step? I did some quick searches and came up with nothing helpful. 

[MiDer · Accepted solution]

a join expression could be an easier way

https://learn.microsoft.com/en-us/power-automate/data-operations#use-the-join-action

[milkdesk]

@MiDer Thank you! I was able to insert both the table and customer name into the email.  

I'm super proud of this. Thanks again for your help.