cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ramzez
Helper I
Helper I

How to create an Html table from data

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.

HtmlTable.png

 

 

 

 

 

 

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:

HtmlTable2.png

 

 

 

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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

 

poweractivate_0-1666683780232.png

 

See if you like it now @ramzez 

View solution in original post

8 REPLIES 8

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

poweractivate_0-1666681393932.png

 

 

 

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

Ventit
Resolver II
Resolver II

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

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

 

poweractivate_0-1666683780232.png

 

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?

@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?

It works perfectly! Thank you for your help!

Abardaje1194
Regular Visitor

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

Abardaje1194
Regular Visitor

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (1,416)