Formula - What to try when numbers don't format correctly

March 24, 2021

3 min read

Formula Formatting

A frustrating problem that app builders sometimes encounter, is that the formula they use to format numbers does not work.

The typical scenario looks like this. An app builder attempts to format a number using the text function. The usage of this function looks like this:

Formula                         Output
---------------------------------------------
Text(60934.2, "#,#.000")   	60,934.200
Text(60934.2, "#,#.###")   	60,934.2
Text(22934.23624, "#.00")	22934.24

https://docs.microsoft.com/en-gb/powerapps/maker/canvas-apps/functions/function-text

When the app builder applies this formula to the text property of a label control, the output remains unformatted.The screenshot beneath illustrates this problem. Notice how we attempt to format the 'acquisition price' value with thousand comma separators and to hide decimal places, yet the text in the label does not display the desired format.

Typical cause of the problem

In almost all cases, the cause of the problem is that Power Apps doesn't recognise the underlying field as a number. There can be for a number of reasons for this.

If we attempt to call the text function on a SharePoint list or database column that is of data type text, the Text function will fail to apply numeric formatting.
If we use an Excel spreadsheet, Power Apps may not recognise the column as numeric.
With SharePoint, Power Apps does not correctly recognise calculated columns as numbers. My post here provides more details on how to fix this problem.
http://powerappsguide.com/blog/post/sharepoint-beware-of-calculated-columns

We can confirm whether Power Apps correctly recognises a field as a number by examining the icon that appears next to the field in the designer. A numeric field displays a "123" icon, as opposed to a "abc" icon.

How to fix the problem

If the data type of the underlying field is text, the preferred fix is to change the data type to a numeric data type.

To set up an Excel spreadsheet so that Power Apps recognises it as numeric, it's important to confirm that none of the cells in the column contain non-numeric text characters (including leading/trailing spaces). We can then select the column, and use the data type drop-down in the ribbon bar to set the data type to 'Number'.

When we add an Excel data source to Power Apps, it's important that the spreadsheet contains some rows with numbers. If we connect an empty spreadsheet to Power Apps, it has no way to determine that a column is numeric when there are no rows.

After we modify the data type of an existing data source, we must refresh the data source from Power Apps through the data panel.

In cases where we are unable to change the data type of the data column, alternative is to convert the text value to a number, before formatting it with the text function. Here's an example of how this formula looks:

Text(Value(Parent.Default), "#,#")

Taking the example that we used earlier, we now notice that this formula properly formats the number with 1000 comma separators.

An alternative formula would look like this:

If(IsNumeric(Parent.Default),
   Text(Value(Parent.Default), "#,#"),
   Parent.Default
)

This formula applies an additional test. If Power Apps cannot convert the text value to a number (due to the existence of non-numeric characters), it falls back to displaying the unformatted value, rather than an empty string.

Conclusion

A frustrating problem that app builders sometimes encounter is the inability to correctly format numbers. The most common cause of this problem is that the data type of the source value is text. We can fix this issue by changing the data type of the source column, or by calling the Value function to convert the text value to a number.