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