cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thejack0fhearts
Frequent Visitor

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
v-xida-msft
Community Support
Community Support

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

11 REPLIES 11
v-xida-msft
Community Support
Community Support

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

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!

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
Helper I
Helper I

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%

Screenshot marked up process.jpgScreenshot Updated.jpg

Cheers, 

 

Clint

Hi, Clint,

 

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


Thank you in advance!

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. 

 

Screenshot 2021-03-25 075932.jpg

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!

Hi @jeneaMD  please see below.

 

11223344

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...

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>

 

Screenshot Compose HTML.jpg

Thank you, @ClintRB!

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,196)