Formatting HTML Table with Compose

[AlineStoll]

Hi all, 

I'm using Power Automate to send some tables through email from PBI. My table has 4 columns and I would like to format third column to comma style and last column to percentage:

I'm using the compose with the following 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: #DC0000;
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>

Could someone please help me with it? I'm new in Power Automate and I am totally lost 😞

[Mister_Shaik]

Hi @AlineStoll ,

I'm assuming that you're passing your values to the compose action to build the table rows and append to a string or variable, in that case you a formatNumber expression to get your desired output.

• Thousands separator or comma formatting:- formatNumber(#ValueHere, '###,###,###.##')
• Percentage: formatNumber(#ValueHere, '00.00%')

Below is an example input and the result,

formatNumber(96845325, '###,###,###.##')	96,845,325
formatNumber(0.685523, '###,###,###.##')	68.55

You can refer below blog for a detailed guide on how this works and different ways of formatting,
https://mistershaik.com/number-formatting-in-power-automate-and-power-apps/ 

Hope this helps 🙂

Kind Regards,

Shaik Sha
________________________________________________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[AlineStoll · Accepted solution]

Hi Shaik, 

Thank you very much for your help, but I was not successful doing it. 

I am extracting my data from Power BI, maybe I am not doing something correct, let me explain to you my step to step:

Step 1: From PBI, I initialize a row number variable

Step 2: I initialized an HTML table

Step 3: I append to string variable

Step 4: I format number

Step 5: I sent to email

When I save show no errors, but when I run, the email is not sent to me. Could you please help me?

Thank you

[AlineStoll]

Maybe, I am doing the append to the HTML table incorrectly. Do you know how should I do it from PBI?

[AlineStoll]

Actually, now I am running and shows the following message: 

[AlineStoll]

{

  "type": "object",

  "properties": {

    "rows": {

      "type": "array",

      "items": {

        "type": "object",

        "properties": {

          "entity": {

            "type": "object",

            "properties": {

              "Power BI values": {

                "title": "Power BI data",

                "type": "array",

                "items": {

                  "type": "object",

                  "properties": {

                    "SalesRep": {

                      "title": "Power BI data SalesRep",

                      "type": "string"

                    },

                    "MTD Bids #": {

                      "title": "Power BI data MTD Bids #",

                      "type": "number"

                    },

                    "MTD Bids $": {

                      "title": "Power BI data MTD Bids $",

                      "type": "number"

                    },

                    "MTD Bids C1": {

                      "title": "Power BI data MTD Bids C1",

                      "type": "number"

                    }

                  },

                  "required": []

                }

              }

            },

            "required": [

              "Power BI values"

            ]

          }

        },

        "required": [

          "entity"

        ]

      }

    }

  },

  "required": [

    "rows"

  ]

}

[Mister_Shaik · Accepted solution]

Hi @AlineStoll ,

You need to do the number formatting inside the "Append to string variable" action, on the number fields that you have.

If you're planning to convert the "MTD Bids $" to a thousands separator format (e.g., $ 123,456,789.12), then the formula would look something like this,

formatNumber(items('Apply_to_each')?['MTD Bids $'],'$ ###,###,###.##')

You can change the expression based on the inputs and the format you want.

Also, if you're planning to send large amounts of emails every day, then I'd suggest you to use the Outlook send email action rather than the Mail connector, as it has low threshold for throttling, below is the throttling limits for Mail actions,

Kind Regards,

Shaik Sha
________________________________________________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[AlineStoll]

Thank you @Mister_Shaik , helped me a lot. I just have an additional question:

I need to attach three different HTML tables in the same email, but when I create the flow to develop second HTML, when I save it, and apply, when I come back Power Automate delete it. Do you know how can I fix it?

When I try to create Table HTML 2, the system deletes it.

Can you please help me? Thank you.

[Mister_Shaik]

Hi @AlineStoll ,

Can you clarify what you mean by delete?

After you generate the HTML Table - are you saving it to some location or sending it out in the email?

Can you please provide how the second HTML table is coming into picture, as I can only see one table being created in the flow screenshot

Kind Regards,

Shaik Sha

[AlineStoll]

Hi @Mister_Shaik ,

I did the following flow, but when I save and run, and come back again to the page, it disappear and I cannot attach the second table. Could you please help me?

[AlineStoll]

In reality @Mister_Shaik I don't know how to prepare the second HTML table. Is it correct?

Thanks

[AlineStoll]

And shows this error message to me: 

[AlineStoll]

Hi @Mister_Shaik , i don't know how to say thank you for so many help you are giving to me!!!

After some hours I figured out how to append the second HTML, however, The flow is not running because shows this error message:

This is the script I am using:

<tr style="background-color:@{if(equals(mod(variables('RowNumber'),2),0),'white','#e1e1e1')};">

<td>@{items('Apply_to_each_2')?['Sales Rep']}</td>

<td>@{formatNumber(items('Apply_to_each_2')?['MTD WINs #'],' ###,###,###.##')}</td>

<td>@{formatNumber(items('Apply_to_each_2')?['MTD WINs $'], '$ ###,###,###.##')}</td>

<td>@{formatNumber(items('Apply_to_each_2')?['MTD WINs C1%'],  '###,###,###.## %')}</td>

If I use just the two first lines, it works, but if I insert the columns with numbers, the flow does not works. 

Could you please help me?

[Mister_Shaik]

Hi @AlineStoll ,

You're getting the error because some of the values passed to "formatNumber" might've been empty or null values.

In this case, we can simply write an if-else logic to give 0 as input to formatNumber expression, when the number value is null. See the updated code below,

<tr style="background-color:@{if(equals(mod(variables('RowNumber'),2),0),'white','#e1e1e1')};">
<td>@{items('Apply_to_each_2')?['Sales Rep']}</td>
<td>@{formatNumber(if(empty(items('Apply_to_each_2')?['MTD WINs #']),0,items('Apply_to_each_2')?['MTD WINs #']),'###,###,###.##')}</td>
<td>@{formatNumber(if(empty(items('Apply_to_each_2')?['MTD WINs $']),0,items('Apply_to_each_2')?['MTD WINs $']), '$ ###,###,###.##')}</td>
<td>@{formatNumber(if(empty(items('Apply_to_each_2')?['MTD WINs C1%']),0,items('Apply_to_each_2')?['MTD WINs C1%']), '###,###,###.## %')}</td>

Hope this helps 🙂

Kind Regards,

Shaik Sha
________________________________________________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[AlineStoll]

Hi @Mister_Shaik , thanks for the update. 

I'm use the if formula but now is showing the following message: 

InvalidTemplate. Unable to process template language expressions in action 'Append_row_to_HTML_table' inputs at line '0' and column '0': 'The template language function 'empty' expects its parameter to be an object, an array or a string. The provided value is of type 'Integer'. Please see https://aka.ms/logicexpressions#empty for usage details.'.

Could you please help me?

[Mister_Shaik · Accepted solution]

Hi @AlineStoll ,

The "empty" expression would've worked if your input was a string type input, as it's a number we just need to tweek and replace it with a equals check,

<tr style="background-color:@{if(equals(mod(variables('RowNumber'),2),0),'white','#e1e1e1')};">
<td>@{items('Apply_to_each_2')?['Sales Rep']}</td>
<td>@{formatNumber(if(equals(items('Apply_to_each_2')?['MTD WINs #'],null),0,items('Apply_to_each_2')?['MTD WINs #']),'###,###,###.##')}</td>
<td>@{formatNumber(if(equals(items('Apply_to_each_2')?['MTD WINs $'],null),0,items('Apply_to_each_2')?['MTD WINs $']), '$ ###,###,###.##')}</td>
<td>@{formatNumber(if(equals(items('Apply_to_each_2')?['MTD WINs C1%'],null),0,items('Apply_to_each_2')?['MTD WINs C1%']), '###,###,###.## %')}</td>

Kind Regards,

Shaik Sha
________________________________________________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[Hambach]

Hi @Mister_Shaik ,

terribly sorry to hijack this old thread but I was having the same problems and followed your adivce here. Thank you. 

But I also get the same problem that formatNumber() is not possible because he can't read NULL values. Some columns work others don't (here in the picture left empty for now):

If I just let it read the actual value without any formatting, he can get the values just fine. This is the HTML output:

The inputs come all from the same table and every value column is formatted the same in the underlying power bi table as percentage:

If I use your above code where it checks for NULLS, it will replace every value in those problem-coluns with 0, of course.

Do you have an idea where the problem might be coming from or how I could work around it? 

Thank you very much!