Power Automate: Format Number action

I wrote about the formatNumber function , the counterpart of the “Format Number” action, in case you need to develop complex formulas to get the data as you need it. But if you want to convert the data and show it to the users, the “Format Number” action is much nicer and simplifies things.

So let’s understand how it works.

Where to find it?

The “Format Number” action can be found in the “Built-in” section.

Select “Number functions.”

Select the “Format Number” action.

Here’s what it looks like.

Now that we know how to find it let’s understand how to use it.

Usage

The first thing to understand is that we always have to provide a number but depending on the format, we may need to give a whole or decimal number.

First, let’s look at the format. Power Automate displays three default formats, but there are a lot more.

The numeric format

The first format will display the information as “#.00” meaning that it will always display two precision values for the decimal part of the number. This means that if the number is whole, it will add two zeros. A second is added if the number only contains one decimal point. Here’s what it looks like:

This representation is excellent when you want to similarly display information to the users. Notice that the number will not be changed in any way, like adding commas to signal the thousands or spaces (in Europe). The only thing is the decimal places that will be reduced or added to make for two decimal points.

Finally, notice that the numbers will be rounded if they are longer than two decimal places.

Please be careful if you want to truncate the values instead of having a rounded representation.

The “nice number” format

The second format will further transform the number. Let’s look at an example first to understand what it’s done.

2

The number will have the same truncation before decimal places, with the exact rounding of values, but notice the comma separation. To our American friends, this would make sense so that the number would look familiar to them. This is where the locale comes into effect, being the default one “en-US,” meaning that the USA’s “style” is the one used.

We’ll cover this a bit further to understand how to convert it to other formats but for now, let’s finish the three formats.

The currency format

The previous formats would format the number, but in the third case, we indicate that we want that the number represented as a currency. Again, the default value is “en-US,” so the number displayed will be displayed as the last option but with a $ sign in the back, like this:

Notice again the “C2” showing up. We’ll discuss this in the next section, but it represents a nice way of using the formatNumber function. It’s the same thing as calling:

formatNumber(12345.345,'C2')

Custom format

Not to make this article huge, I’ll abbreviate it, but if you want to get the full details, you can check the formatNumber function that contains all the details for each format.

You can provide a custom value to your format like this:

Here’s the result:

Again there are a lot of formats, and I went into a lot of detail in the formatNumber function, so anything that you use there, you can use here.

Locale

The locale means you can pick the regional settings where you want the values to be presented. So let’s think about the currency example above. Let’s change it to Portugal and see what we get.

It’s what I would expect for a number formatted here. The Euro symbol in the end and the spaces separating the hundreds in the number. In the en-US, for example, it would be a comma and not a space.

There are a lot of variations and hundreds of languages and regions, so please be sure to use them wisely.

Limitations

The “digits” format in the “Format Number” action (if you use “D2,” for example, in the format option) can only accept whole numbers. I can understand since it could be confusing since the number represented after the “D” string could be interpreted as the overall digit (considering the decimal part) or not. But in this case, since others allow for decimal values, it may confuse some users.

Recommendations

Here are some things to keep in mind.

Always use the locale

Although it’s flagged as optional, I strongly recommend using the locale and showing the values always in the format that the users will understand. As mentioned above, in Europe, the value “100,233” is not “hundred thousand two hundred and thirty-three,” but it’s “hundred point two hundred and thirty-three.” The value as a string is correct but different locations in the world will think of numbers differently, so having the “Format Number” action in your Flows can be both a blessing and a curse, if you don’t use it properly.

Name it correctly

The name is super important in this case since a number can represent anything. Indicate clearly what is the number’s meaning. Always build the name so that other people can understand what you are using without the need to open the action and check the details.

Always add a comment

Adding a comment will also help avoid mistakes. Indicate where the number comes from, for example, if it’s calculated and how. Also, indicate why you are choosing the options, and if you calculate dynamically, the locale or the format indicate the reasoning. It’s essential to enable faster debugging when something goes wrong.

Always deal with errors

Have your Flow fail graciously and notify someone that something failed. It’s horrible to have failing Flows in Power Automate since they may go unlooked-for a while or generate even worse errors. I have a template that you can use to help you make your Flow resistant to issues. You can check all details here.

Back to the Power Automate Action Reference.

Photo by Arno Senoner on Unsplash