Group multiple Excel rows into an HTML table

[thejack0fhearts]

Hi, guys! With some help from you all, I've built a flow which pulls data from an Excel row, turns it into an HTML table, and then sends an email with the table as a .doc attachment.

Here's the flow (the condition is just to filter the data down for testing, and the first two compose actions are just to reformat some dates):

This flow currently sends one email per row with just the data from that row in the attached table. This is an improvement from sending all the rows in each email, but I realize now I need to be able to group rows with the same ID into one table and email. Is this possible to do in Flow?

Here a sample from the data set:

Thanks in advance for your help!

[v-xida-msft · Accepted solution]

Hi @thejack0fhearts,

Could you please share a bit more about the ID that you mentioned? Is it the row id of the excel file or the ID of the client?

Further, could you please show a bit more about the data structure of your excel file?

If the ID that you mentioned is the row id of your excel file, I think it is impossible to do that in Microsoft Flow currently. Each row of your Excel file has different row id, so we could not group rows with same row id.

If the ID that you mentioned is the ID of the client, I suppose that you add a ID column within your Excel file, is it true? I have create a Excel file in my OneDrive for Business folder and the data structure of it as below:

Note: The ID column is a custom column which is used to identity a client.

If you want to group rows with same client ID, I have made a test on my side and please take a try with the following workaround:

• Add a proper trigger, here I use Flow Button trigger.
• Add a "Get rows" action, specify File Name and Table Name.
• Add a Variables-> "Initialize variable" action, Name set to Client ID, Type set to Array and Value set to empty.
• Add a "Apply to each", input parameter set to output of "Get rows" action. Within "Apply to each", add a "Append to array variable" action, Name choose Client ID and Value set to ID dynamic content of "Get rows" action.
• Under "Apply to each", add a "Compose" action, Inputs set to following formula:

• union(variables('Client ID'),variables('Client ID'))

  Add a "Do Until" action, within Condition box, click "Edit in advanced mode", type the following formula:

• @empty(variables('Client ID'))

  Within "Do Until" action, add a "Filter array" action, From set to output of "Get rows" action, then click "Edit in advanced mode", type the following formula:

@equals(item()?['ID'], first(variables('Client ID')))

Add a "Select" action, From set to output of "Filter array" action, within Map entry, type the entries as below:

Note: Within Enter key box, type the Column Name. Within Enter value box, type the following formula:

item()?['ColumnNameOfYourExcelFile']

For Excel table that contain column names with spaces, we should specify each space as "_x0020_". More details about the usage of "_x0020_", please check the following article:

https://docs.microsoft.com/en-us/powerapps/common-issues-and-resolutions#recently-addedchanged

Add a "Create HTML table" action, From set to output of "Select" action. Then add a "Send an email" action, Attachments Name field set to GroupRows.doc and Attachment Content set to output of the "Create HTML table" action.

Add a "Compose 2" action, Inputs set to following formula:

skip(variables('Client ID'),1)

Add a "Set variable" action, Name choose Client ID and Value set to output of "Compose 2" action.

Image reference:

The flow works successfully as below:

Best regards,

Kris

[thejack0fhearts]

Thanks for your help, Kris! I used a combination of your flow and the flow listed here to make it work. I ran into the 2048 row limit from Excel, but everything else seems to be working.

Thanks again!

[vshah1]

Thanks Kris 

This is very helpful. However I am facing a challenge in the number of emails that get triggered basis this flow.  The equiavalnt of ID for me is Business leader. However I have the following 2 challenges 

(1) How do I trigger a flow where the email is sent to the each business leader just for his/her portion of the excel sheet 

(2) How do I adjust the count in the do until based on the number of business leaders. For example if I have 5 business leaders do I need to manually choose 5 in the count column in do until ?

Thanks a lot in advance for your help

[ClintRB]

Hi, Thanks for the lead to a solution unfortunately I had spent a lot of time trying to work out why the original solution didn't work 100%.  It would work for the first instance but then would cycle through every line and send an email for every like instance. 

There are two problems in this flow.

First, the Compose Union step needs to be inside the Do Until step (at the top)

Secondly, after the email the skip step needs to refer to the Union output not the variable output. 

You can also see in the original image that the number of Do Until steps is 5 which is the same as the Apply To Each step.  The Do Until steps should be less is there are multiples of the same variable.

Below are the original and then the version that worked 100%

Cheers, 

Clint

[jeneaMD]

Hi, Clint,

Could you please share your "Filter Array" screenshot? I'm still facing an issue with this step.

Thank you in advance!

[ClintRB]

Hi @jeneaMD ,

Please see image.

The information is coming from several tables, one has the names and email addresses and the other is a monthly generated report.  You can still use this example directly from a sharepoint list. 

[jeneaMD]

Thank you for help, @ClintRB!

I have made changes according to your last screenshots, but still it doesn't work as expected.

I just saw that inside "Do until" you have 3 "Compose" steps instead of 2 like it was at the beginning. In order to see the all uncleared steps for me, could you please also share all the steps starting with "Create HTML Table"?

Thank you one more time in advance!

[ClintRB]

Hi @jeneaMD  please see below.

1234

[jeneaMD]

You are a real hero for me, @ClintRB! 🙂

But how does it look "Compose 2 Format HTML" and "Compose HTML OT TABLE"? What are the expressions for these 2 steps?

It seems each of these 2 Compose steps are corresponding to your 2 Excel tables...

[ClintRB]

Hi @jeneaMD 

https://ryanmaclean365.com/2020/01/29/power-automate-html-table-styling/

Scroll down to find the code

<style>
table {
border: 1px solid #1C6EA4;
background-color: #EEEEEE;
width: 100%;
text-align: left;
border-collapse: collapse;
}
table td, table th {
border: 1px solid #AAAAAA;
padding: 3px 2px;
}
table tbody td {
font-size: 13px;
}
table thead {
background: #1C6EA4;
border-bottom: 2px solid #444444;
}
table thead th {
font-size: 15px;
font-weight: bold;
color: #FFFFFF;
border-left: 2px solid #D0E4F5;
}
table thead th:first-child {
border-left: none;
}
</style>

[jeneaMD]

Thank you, @ClintRB!