How to create an Html table from data

[ramzez]

Hi all,

I need help with formatting my data into an Html table that I will display in an HtmlText control.

My data is as follows:

ClearCollect(
    colData, 
{Name:"Person 1", TaskName:"Task 1", Outcome:"Failed"},
{Name:"Person 1", TaskName:"Task 1", Outcome:"Passed"},
{Name:"Person 1", TaskName:"Task 2", Outcome:"Passed"},
{Name:"Person 1", TaskName:"Task 3", Outcome:"Passed"},
{Name:"Person 2", TaskName:"Task 1", Outcome:"Passed"},
{Name:"Person 2", TaskName:"Task 2", Outcome:"Passed"},
{Name:"Person 2", TaskName:"Task 3", Outcome:"Passed"},
{Name:"Person 3", TaskName:"Task 1", Outcome:"Passed"},
{Name:"Person 3", TaskName:"Task 2", Outcome:"Failed"},
{Name:"Person 3", TaskName:"Task 2", Outcome:"Failed"},
{Name:"Person 3", TaskName:"Task 2", Outcome:"Passed"},
{Name:"Person 3", TaskName:"Task 3", Outcome:"Passed"}
);

I need to format the data into the following kind of a table dynamically so that the amount of Persons, TaskNames and Outcomes can change.

Here is the html for the table in the picture but this needs to be dynamic.

"<table border='1' style='border:1px solid black; border-collapse:collapse'>
<tr>
    <td></td>
    <td>Task 1</td>
    <td>Task 2</td>
    <td>Task 3</td>
</tr>
<tr>
    <td>Person 1</td>
    <td>Failed<br>Passed</td>
    <td>Passed</td>
    <td>Passed</td>
</tr>
<tr>
    <td>Person 2</td>
    <td>Passed</td>
    <td>Passed</td>
    <td>Passed</td>
</tr>
<tr>
    <td>Person 3</td>
    <td>Passed</td>
    <td>Failed<br>Failed<br>Passed</td>
    <td>Passed</td>
</tr>

</table>"

 I am able to create this table:

dynamically with this code:

"<table border='1' style='border:1px solid black; border-collapse:collapse'>
<tr>
    <td></td>
    <td>" & Concat(Distinct(colData,TaskName), Result, "</td><td>" ) & "</td>
</tr>
<tr><td>" & Concat(Distinct(colData,Name), Result, "</td></tr><tr><td>") & "</td></tr>

</table>"

 but I am stuck in how to progress further.

[poweractivate]

@ramzez 

Try this for the HtmlText property of the Html Text Control:

With
(
    {_g1:GroupBy(colData,"Name","TaskName","MyOutcome")}
    ,$"<table border='1' style='border:1px solid black; border-collapse:collapse'>
     <tr>
        <td></td>
        <td>{Concat(Distinct(colData,TaskName), Result,"</td><td>")}</td>
     </tr>
     <tr>
        <td>Person 1</td>
        <td>{Concat(Filter(_g1,Name="Person 1"),Concat(MyOutcome,Outcome,"<br>"),"</td><td>")}</td>
     </tr>
     <tr>
        <td>Person 2</td>
        <td>{Concat(Filter(_g1,Name="Person 2"),Concat(MyOutcome,Outcome,"<br>"),"</td><td>")}</td>
     </tr>
     <tr>
        <td>Person 3</td>
        <td>{Concat(Filter(_g1,Name="Person 3"),Concat(MyOutcome,Outcome,"<br>"),"</td><td>")}</td>
     </tr>

     </table>"
)

which results in:

I'll see if I can improve it further as well.

[Ventit]

I use a basic template approach for all my HTML tables and just modify.  Here is a recent one that may help.  It replicates data from a Sharepoint list based on a filter

"</table> <p></p>"

&

"
<p> <span style='font-weight:bold'></span></p>
<table border='1' style='width:90%'>
<tr>
<th style='width:10%; padding-left:1em; padding-right:1em; white-space: nowrap'>Date</th>
<th style='width:10%; padding-left:1em; padding-right:1em; white-space: nowrap'>Time</th>
<th style='width:15%; padding-left:1em; padding-right:1em; white-space: nowrap'>Booked By</th>
<th style='width:15%; padding-left:1em; padding-right:1em; white-space: nowrap'>PMKeys</th>
<th style='width:15%; padding-left:1em; padding-right:1em; white-space: nowrap'>Contact Phone</th>
<th style='width:25%; padding-left:1em; padding-right:1em; white-space: nowrap'>Task</th>

</tr>"

&

Concat(Filter(SortByColumns(Reminder_List,"Datenumber",Ascending,"Time",Ascending),Date >= Today() And Title = "National"), "<tr>
<td style='text-align:left; padding-left:1em; padding-right:1em; white-space: nowrap'>"& Text(Date,"dd/mm/yyyy") &"</td>
<td style='text-align:left; padding-left:1em; padding-right:1em; white-space: nowrap'>"& Substitute(Text(Time*100,"00:00"),":5",":3") &"</td>
<td style='text-align:left; padding-left:1em; padding-right:1em; white-space: nowrap'>"& Upper(BookedFor) &"</td>
<td style='text-align:left; padding-left:1em; padding-right:1em; white-space: nowrap'>"& PMKeys &"</td>
<td style='text-align:left; padding-left:1em; padding-right:1em; white-space: nowrap'>"& ContactPhone &"</td>
<td style='text-align:left; padding-left:1em; padding-right:1em; white-space: nowrap'>"& Task &"</td>


</tr>
")

&

"</table>"

[poweractivate · Accepted solution]

@ramzez 

Really great news, I think I improved it much further now:

Try this for the HtmlText property of the Html Text Control:

With
(
    {_g2:GroupBy(colData,"Name","MyTasksByName")}
    ,$"<table border='1' style='border:1px solid black; border-collapse:collapse'>    
     <tr>
        <td></td>
        <td>{Concat(Distinct(colData,TaskName), Result,"</td><td>")}</td>
     </tr>
     <tr><td>
     {Concat(_g2,$"{Name}</td><td>{Concat(GroupBy(MyTasksByName,"TaskName","MyOutcome"),Concat(MyOutcome,Outcome,"<br>"),"</td><td>")}","</td></tr><tr><td>")}</td></tr>

     </table>"
)

The results are:

See if you like it now @ramzez 

[ramzez]

Thank you for your suggestion! That works perfectly if the amount of persons / rows is fixed. How can I get the number of persons / rows to be dynamic?

[poweractivate]

@ramzez 

---

@ramzez wrote:

Thank you for your suggestion! That works perfectly if the amount of persons / rows is fixed. How can I get the number of persons / rows to be dynamic?

---

I just provided you with new response, it is much improved and addresses this exactly.

Does it work for you?

[ramzez]

It works perfectly! Thank you for your help!

[Abardaje1194]

Hello, 

Apologies if I just reply here as I don't know but I can't post anything in the community blog and this is my first time to use it. Anyway, my question, I need help with formatting my data into an Html table in power apps and the column 2 will get the data from SP list or gallery. Here's the sample: 

Summary

Sample 1			Test 1		USD
Sample 2			Test 2		USD
Sample 3			Jan-2022		Test 3
Sample 4			Dec-2021		Test 4	MM/DD/202X
Text | text | number					Test 5	Buy:		Sell:

Country			Data	In mil USD	Limit as %
Try 1			Sample1	000.0	0.0%
Try 2			Sample2	000.0	0.0%
Try 3			Sample3	000.0	0.0%
Try 4			Sample4	000.0	0.0%

Profile