Number formatting in Power Automate and Power Apps

Hello everyone and welcome to my first ever blog!

 

Today we’re going to discuss on how we can perform all kinds of formatting on Numbers using Power Apps and Power Automate. We might have come across scenarios in our day to day business where we need to either convert our numbers values from one region’s format to another format for displaying and saving, or we don’t want to have a plain old number field as the row or item identifier, instead want some formatting applied to it. it might be something like PRJ-1xxx for project tracking or Part-xxx for tracking parts etc.,

 

So today we’re going to learn how to convert our number fields and values to different regional formats, as well as how to apply special formatting to them, using both Power Automate and Power Apps.

 

Let’s get started!

 

Applying number formatting in Power Automate

 

Out of the box Power Automate has an action and an expression to format number values, it’s called “formatNumber” expression – “Format Number” action.

 

To format a number to any regional format is as simple as passing the number, the format that you want it to look like, and the regional value.

 

 

 

In the above snip, I’m basically trying to format a given decimal number with a thousands separator, in both English and German regions. The first field “Number” takes in the number value to be formatted, the “Format” field is used to specify what sort of formatting we want it to perform, and the third field “Locale” basically defines which regional format to convert the number to. You can find an equivalent example when you’re using expressions below,

English - formatNumber(9876543.21,'$ ###,###,###.##','en-US')
German - formatNumber(9876543.21,'$ ###,###,###.##','de-DE')

 

The output of “Format Number” actions we’ve used, will give the following results

 

 

 

You might notice, that when I’ve passed the region as US, it added thousands separator exactly as I’ve defined. But when it comes to the German region, the comma and dot are interchanged, because that’s how the numbers are formatted in german.

 

There we go! We now have our numbers formatted with thousands separator, including the constraints of different regions as well!

 

You can follow the same process to generate different kinds of formatted outputs for your number values.

 

Format	Number
formatNumber(123,’00000′)	00123
formatNumber(123,’######’)	123
formatNumber(123,’10000′)	10123
formatNumber(123,’PRJ-10000′)	PRJ-10123
formatNumber(123.2,’##.00′)	123.20
formatNumber(9,’PRJ-10#’)	PRJ-109
formatNumber(0.685565,’00.0%’)	68.55

 

You might notice that wherever I’ve used the zero in the formatting, the output will have those zeros if the number has fewer digits than there are zeros in the format. But the #(Pound) doesn’t return extra zeros when the number has fewer digits than the pounds in the format. You can notice the behaviour from the first 2 examples in the above table.

 

Try using the expressions wherever possible rather than the Format number action, if you imagine a flow where you have tens of locations that need formatted numbers as input then using Format Number action will fill up the entire flow UI, but using expressions will result in adding no additional actions

 

 

Applying number formatting in Power Apps

 

In Power Apps you might be having a data source which has a number field, but when displaying that number field data, you may want to format it and then display. You can achieve it fairly easily, using the Text formula.

 

The Text function can convert any a number value to a text representation using a given formatting. The Text function expects it’s first input to be the value to convert to text, the second property is the formatting to be applied and the third property is language.

 

In the below snip, I’ve used Text function to add thousands separator to the number in both US format and German format

 

From the above images, you can see that I’ve used different formulas to format numbers when it comes to US and DE versions. That’s because when it comes to regional based formatting, it’s not as straight forward as we have it in flows.

 

Below, you can find the results of different formatting when applied to a number value

 

Formula	Number
Text(123, “00000”)	00123
Text(123, “#####”)	123
Text(123, “10000”)	10123
“PRJ” & Text(123, “-10000”)	PRJ-10123
Text(123.2, “##.00”)	123.20
“PRJ” & Text(9, “-10#”)	PRJ-109

 

Hope you found this post helpful 🙂

 

Until Next Time!