cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
thejack0fhearts
Level: Powered On

Group multiple Excel rows into an HTML table

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):

 

Capture.PNG

 

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:

 

Capture2.PNG

 

Thanks in advance for your help!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Group multiple Excel rows into an HTML table

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:2.JPG

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:3.JPG

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:

4.JPG5.JPG6.JPG7.JPGThe flow works successfully as below:8.JPG

 

9.JPG

 

10.JPG

 

11.JPG

 

 

Best regards,

Kris

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Group multiple Excel rows into an HTML table

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:2.JPG

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:3.JPG

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:

4.JPG5.JPG6.JPG7.JPGThe flow works successfully as below:8.JPG

 

9.JPG

 

10.JPG

 

11.JPG

 

 

Best regards,

Kris

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

thejack0fhearts
Level: Powered On

Re: Group multiple Excel rows into an HTML table

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
Level: Powered On

Re: Group multiple Excel rows into an HTML table

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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,516)