Power Automate: How to convert a string to formatted currency?

Not all numbers (or strings that are numbers) are the same so we should not treat them as such. Some could be a simple number without a format, and others could mean currency that is different from country to country.

Power Automate allows us to format numbers depending on what they are or where you are. For example, if you want to represent a number that is a currency in US format ($1 000,00 for instance), then you could try to do it yourself, but you would be in a world of hurt since the number can be of any size. Instead, some functions and actions can help us, so today we’ll take a look at how to convert and personalize a number to a culture or country with a simple action or function.

Converting using an action

We have an action built for this, called the “Format number” action. It looks like this:

We have to provide it three parameters:

1. The number to be converted
2. The format that we want
3. The locale or country we want to convert it to.

Let’s look at each of them separately.

The number

We need a number to use this action. If you have a string that contains only a number, you still need to convert it to an integer using the “int function”.

Simple enough so far.

The format

There are multiple formats for numbers, depending on what they are. Here’s the overview of the options:

We’re interested in the last option that represents the number as a currency value that will add the currency symbol. So if we provide the number “10000.133” we’ll get:

Notice the format “C2”. Well, go back to it in the next section.

The Locale

The locale adjusts the number to the country and currency to make sense to the person reading it. For example, if we change it to Portugal.

We’ll get the following:

The expected result makes sense to us because we:

1. Put the currency symbol at the end
2. Use the comma to separate the decimal part
3. Use spaces to separate the thousands

Depending on your country, you’ll get different results, which is the point. You can provide other numbers representations depending on the locale or fit person.

Using a function

But we also have a function to do this called “formatNumber”. This function works the same way as the “Format Number” action with the difference that you can include it anywhere. In our example, we’ll use a compose action, but you can use it anywhere you have a number and can write a formula.

The function has the following syntax:

1. Number to convert
2. Format
3. Locale

Sounds familiar? The parameters are the same as above but in the string representations. So to do the same as above, we will use “C2” as the format and the “pt-pt” as the locale. Let’s see it in action:

formatNumber(10000.133,'C2','pt-pt')

We get the same result.

Let’s put them side by side to compare:

I recommend using the “Format Number” action when you can since it’s a lot easier to use, but if you know what you’re doing, you can also write the expression if it’s easier.

Bulk changes

As a bonus, let’s look at how to change all the values in an array of elements. To do this, we will use a simple compose action that will generate an array and the “select” action to convert all values at once. Here’s the array:

createArray(123.3,122.3,1111.11)

Now let’s put them in a “select” action.

Notice two important things:

1. We don’t have an “Apply to each” action because the “select” action takes care of everything.
2. We are applying the formula on the right side to each item with the formula:

formatNumber(item(),'C2','pt-pt')

The “item” function does some of the heavy lifting providing the function of an individual item each time.

Let’s see the result:

Notice that we get a JSON array. Power Automate will automatically transform the result into a JSON array, so consider this when parsing the return object. Technically, Power Automate will think of everything inside the array as a JSON array, even if it’s a list of values.

Final thoughts

So if you want to convert a number or a string into currency please don’t do it manually. Microsoft spend some time implementing this properly and since we both have an action and a function there’s no real reason to not use them.

Photo by Jason Leung on Unsplash