Others

How to Show Two Decimal Places in Power Automate HTML Tables

Phi Hung Vo Sep 30, 2026 869 views

Question details

The user needs to retain trailing zeros and format numeric values to two decimal places when generating HTML tables from Excel data in Power Automate, as well as calculate accurate totals.

How to Show Two Decimal Places in Power Automate HTML Tables

Product: Excel, Power Automate
Device & OS: not provided
Scenario: Pulling financial data from an Excel spreadsheet to generate properly formatted HTML tables using Power Automate workflows.
Observed behavior: Power Automate automatically strips trailing zeros from Excel currency values (e.g., changing $24.00 to 24), causing HTML tables to display inconsistently formatted numbers.

Before you start

Ensure your source Excel spreadsheet has the relevant columns formatted properly and remove any pre-calculated Grand Total rows before importing the data into Power Automate.

Solution 1Recommended

Use the Format Number Action and XPath for Totals

Correct your source Excel formatting, then use Power Automate's native 'Format number' action and an XPath expression to display consistent decimal places and calculate totals efficiently.

Because Power Automate reads the raw numeric values from Excel rather than their display formatting, trailing zeros are dropped. You must explicitly reformat these numbers within the flow and use proper variable structures to handle multiple locations or rows.

Prepare your Excel data

Open your source Excel table. Format the Amount column as 'Number' (not Currency) and completely delete any existing Grand Total rows at the bottom of your table to prevent Power Automate from treating the total as a standard data row.

Initialize the array variable

At the beginning of your Power Automate flow, add an 'Initialize variable' action. Set its type to 'Array' and set the initial value to an empty array by simply typing [] in the value field.

Map and format the numeric values

Use a 'Select' action to map your array values using the expression item()?['Amount']. Next, insert a 'Format number' action targeting those selected values, and apply a format string like '0.00' to enforce exactly two decimal places for your HTML table.

Calculate the totals without extra loops

To get the grand total without nesting another 'Apply to each' loop, build an XML object containing your numerical array (using a Compose action). Then, use a compose expression like xpath(xml(outputs('Compose_Name')), 'sum(/root/Numbers)') to calculate the sum.

Use the Format Number Action and XPath for Totals

Placement of Actions: Make sure your row-specific actions (like appending to the array variable) are placed securely inside the correct 'Apply to each' loop block. Otherwise, Power Automate will reject your append expressions as invalid.

Free Microsoft Office alternative

Prepare Your Spreadsheet Data Easily with WPS Office

While Power Automate handles your automated workflows, you still need a robust spreadsheet editor to prepare, format, and organize your source data. WPS Office provides a free, lightweight alternative to Microsoft Excel with complete compatibility for .xlsx files, making it the perfect companion for managing your automation datasets.

1. Open your data file: Launch WPS Spreadsheet and open the .xlsx file you intend to use as your Power Automate data source.
2. Format your number columns: Highlight the columns containing your amounts, right-click, and select 'Format Cells'. Choose the 'Number' category and set decimal places to 2.
3. Save and sync: Save your file. The strict formatting will be embedded into the workbook, ensuring a clean data structure when read by your workflow.

Fully compatible with Microsoft Excel (.xlsx) formats, ensuring seamless integration with Power Automate workflows.Easily apply precise number formats, currency symbols, and decimal places to your columns before automation.Lightweight, fast installation with a familiar interface that requires zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Automate remove trailing zeros from my Excel amounts?

When Power Automate retrieves data from Excel, it reads the raw underlying numeric value, not the visual formatting applied in the spreadsheet. As a result, a formatted value like $24.00 is pulled simply as 24, dropping the currency symbol and trailing zeros.

What value should I use when initializing an array variable in Power Automate?

When setting up an 'Initialize variable' action for an array, you should set its initial value to an empty array. You can do this by typing an open and close square bracket: [].

How can I avoid using multiple 'Apply to each' loops for calculating totals?

You can calculate totals efficiently by converting your array of composed numbers into an XML format. Once converted, apply an XPath sum expression, such as xpath(xml(outputs('Your_Compose_Name')), 'sum(/root/Numbers)'), which instantly calculates the total without iterating through a loop.

Why is my expression in the 'Append to array variable' action showing as invalid?

This error typically occurs if the expression syntax is malformed or if the action itself is placed outside of the necessary 'Apply to each' loop. Row-specific data processing must happen inside the loop that iterates over your Excel rows.

Office Quick Solutions