Convert Strings to Currency in Power Automate: A Practical Guide

Power Automate is an indispensable tool for automating a wide range of business processes, from managing invoices and purchase orders to handling employee expenses and sales reports. In many of these financial scenarios, currency values are often received as plain text strings from various sources like Excel, SharePoint, Forms, Dataverse, APIs, or other external systems. Before these values can be accurately displayed or processed, they typically need to be converted into a proper currency format.

Fortunately, Power Automate provides robust built-in functionality to achieve this through the Format Number action and the formatNumber() expression. This guide will walk you through the process with practical examples and best practices.

Why Convert Strings to Currency?

The need to convert string values to a currency format arises in numerous business contexts:

Invoice Generation: Ensuring invoice amounts are displayed professionally.
Sales Reports: Presenting sales figures in a clear, readable currency format.
Expense Management: Standardizing how employee expenses are recorded and reported.
Purchase Orders: Displaying order totals accurately.
Financial Dashboards: Visualizing financial data with correct currency symbols and formatting.
Email Notifications: Sending out financial updates or alerts with properly formatted currency.

The general workflow for this conversion involves taking a String Value, converting it to a number, and then applying the desired Currency Format to Display the Result.

Method 1: Using the Format Number Action

Power Automate offers a user-friendly, built-in action called Format Number, which simplifies the conversion process.

Steps:

Add Action: Search for and add the Format Number action to your flow.
Input: In the Number field, provide the string value you want to convert (e.g., 12500.75).
Select Format: Choose Currency from the Format dropdown.
Choose Locale: Select the appropriate locale that defines the currency symbol and formatting rules (e.g., en-US for US Dollars).

This action will output the value in the specified currency format, such as $12,500.75. The Format Number action supports a wide variety of locales, allowing you to adapt to different regional currency standards.

Method 2: Using the formatNumber() Expression

For more dynamic or complex scenarios, the formatNumber() expression offers greater flexibility.

Syntax:

The basic syntax for the formatNumber() expression is:

formatNumber(
  Number,
  'Format',
  'Locale'
)

Example:

To format the number 12500.75 as US Dollars:

formatNumber(
  12500.75,
  'C',
  'en-US'
)

This expression will yield the output: $12,500.75. The 'C' format specifier is specifically used for currency formatting.

Converting a String to Currency

When dealing with a string value, such as "12500.75", you first need to convert it to a numeric type before formatting. This is commonly done using the float() function.

Expression:

formatNumber(
  float('12500.75'),
  'C',
  'en-US'
)

Output: $12,500.75

Here, the string is first converted to a floating-point number using float() and then formatted as currency.

Example Using the Compose Action

You can use the Compose action to test and demonstrate this expression. For instance, to format 15000.5:

formatNumber(
  float('15000.5'),
  'C',
  'en-US'
)

Output: $15,000.50

Currency with Specific Decimal Places

To ensure a consistent number of decimal places, you can modify the format specifier. For example, 'C2' enforces two decimal places.

formatNumber(
  float('12345.678'),
  'C2',
  'en-US'
)

Output: $12,345.68

Different Currency Formats

The locale parameter is crucial for displaying the correct currency symbol and formatting. Power Automate automatically adjusts these based on the selected locale:

USDollar:
formatNumber(float('5000'), 'C', 'en-US')
Output: $5,000.00
British Pound:
formatNumber(float('5000'), 'C', 'en-GB')
Output: £5,000.00
Euro:
formatNumber(float('5000'), 'C', 'fr-FR')
Output: 5000,00€

Using Dynamic Content

In real-world scenarios, you'll often work with dynamic content from previous steps. For example, if you have an Excel file with an 'Amount' column containing string values:

Expression within an 'Apply to each' loop:

formatNumber(
  float(items('Apply_to_each')?['Amount']),
  'C',
  'en-US'
)

This expression retrieves the 'Amount' value from the current item in the loop, converts it to a float, and then formats it as US currency. This formatted value can then be used in subsequent steps, such as sending an email or updating a record.

Real-World Examples

Expense Management

An employee submits an expense of 2500.50, which is stored as a string. A Power Automate flow can convert this to $2,500.50 and include it in an email notification:

Email: Expense Amount: $2,500.50

Invoice Generation

For an invoice with an Invoice Amount of 35000:

formatNumber(
  float('35000'),
  'C',
  'en-US'
)

Output: $35,000.00

SharePoint List Example

If a SharePoint list column named 'Amount' contains the value 50000 as a string, a flow can:

Get Item from SharePoint.
Use the Format Number action or expression to convert the 'Amount' to $50,000.00.
Update Item with the formatted currency value.

Common Functions Used

Here are some essential functions you'll often use in conjunction with currency formatting:

Convert String to Number: float() (for decimal values) or int() (for whole numbers).
Currency Formatting: formatNumber().
Mathematical Operations: mul() (multiply), add() (add), etc., for calculations before formatting.

Best Practices

To ensure your currency conversions are robust and reliable, follow these best practices:

Always Convert String First: Use float() or int() before applying currency formatting to avoid errors.
Use the Correct Locale: Select the locale (e.g., en-US, en-GB, fr-FR) that matches your target currency and region.
Handle Null Values: Implement checks to ensure you don't attempt to convert null or empty values, which can cause flow failures.
Standardize Currency Display: Use a consistent format across all reports and communications for a professional look.
Test Different Regions: If your application serves international users, test the formatting with various locales to ensure accuracy.

Common Challenges

Be aware of potential issues:

Invalid String: Attempting to convert non-numeric strings (e.g., ABC123) will result in an error.
Null Values: Blank or null input values can cause the conversion to fail if not handled.
Locale Mismatch: Using the wrong locale can lead to incorrect currency symbols or separators (e.g., using en-US for a European currency).
Decimal Precision: Without specifying precision (like 'C2'), you might get inconsistent decimal places.

Benefits of Automated Currency Formatting

Better Financial Reporting: Standardized currency display enhances readability and accuracy.
Improved User Experience: Easy-to-understand currency values for end-users.
Professional Communications: Currency is automatically formatted in emails and documents.
Reduced Errors: Minimizes manual data entry and formatting mistakes.
Automated Process: Eliminates the need for manual intervention in formatting.

Workflow Summary

The typical workflow for converting strings to currency in Power Automate looks like this:

String Value → float() → formatNumber() → Currency Format → Display/Email/Report

Conclusion

Converting string values to properly formatted currency in Power Automate is a fundamental requirement for many financial and business processes. By leveraging the Format Number action or the formatNumber() expression, combined with the float() conversion function, you can effortlessly display numeric data as localized currency. This capability is invaluable whether you are processing data from Excel, SharePoint, Dataverse, or generating invoices, expense reports, and sales summaries. Power Automate's built-in formatting features ensure consistency, professionalism, and accuracy across all your automated workflows.