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.
Solved! Go to Solution.
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
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.
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>"
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
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?
It works perfectly! Thank you for your help!
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
User | Count |
---|---|
252 | |
106 | |
95 | |
50 | |
39 |