SharePoint - Beware of numeric calculated columns!

There is a known issue that affects SharePoint calculated columns. The problem is that Power Apps fails to identify the data type for numeric calculated columns, and this can prevent app builders from formatting the number in the way that they want.

This issue crops up occasionally and we can see an example of it here:

In this post, we'll recreate this problem and look at the formulas we can use to work around this issue.

Demonstration

To demonstrate this example, let's take a SharePoint list of properties. This list contains an acquisition price column, and a calculated column that converts the acquisition price into US dollars by multiplying by a exchange rate.

The definition of our calculated column is shown here. As a point of interest, note how we specify 2 decimal places in the settings.


Creating a Sample Power App
Next, we'll create an auto generated app. To display our calculated field, we'll add it to our detail form. This provides an initial indication of the problem because an 'abc' icon appears next to the calculated 'AquisitionPriceUSD' field, rather than a numeric '123' icon (shown beneath).



If we now play our app and open the details screen for a record, we see the problem that app builders typically report - there are 9 decimal places after the decimal point.

Fixing this issue

The underlying fix for this problem is to convert the string value to a number and to do this, we call the Value function. We can then call the round function to round the number to 2 decimal places (or to whatever number of decimal places we want).

Round(
Value(ThisItem.AquisitionPriceUSD),
2
)

To illustrate our detail form example, this fix requires us to unlock the card and to to modify the Default property of the card like so:


To apply a more specific format (for example, one that includes comma thousand separators), we would convert the value to a number, and convert it back to string by calling the Text function and passing a custom format string.

Text(
Value(ThisItem.AquisitionPriceUSD),
"[$-en-US]#,#.00"
)


Conclusion

Power Apps does not correctly recognise numeric SharePoint calculated columns as numbers, and this can cause problems with formatting. We can resolve these issues through the help of the Value and Text functions.