Power Automate: coalesce function

The “coalesce function” is one of those hidden gems in Power Automate that can save you much time and make your flows more robust.

I’m saying “hidden gem” because I see many people not using it and then dealing with crashes in the Flows that could be avoided. I’ve done this in the past as well, hence the article.

It does something quite simple. It checks if the first value that isn’t null and returns it. I say the first because we can have multiple, but I’m getting ahead of myself. Let’s look at how to find it and use it.

Where to find it?

You can find the function in every action where a formula is supported. For example, let’s look at a “Compose” action:

As you can see, we can auto-complete by using the “tab” key (old UI) or “enter” key (new UI). Let’s look at how to use it.

Usage

The “coalesce function” follows a simple pattern:

coalesce(<expression1>, <expression2>, ...)

Where:

• You can provide two or more expressions as parameters
• The function evaluates each expression in order
• It returns the first expression that doesn’t evaluate to null
• If all expressions evaluate to null, the function returns null

Let’s start with a simple example. Imagine getting data from a form, but some fields might be empty. You want to have a default value in case the user doesn’t provide input:

coalesce(triggerBody()?['text_1'], 'Guest')

In this example, if the text_1 exists in the trigger body, it will be used. If it’s null or doesn’t exist, ‘Guest’ will be returned instead.

Here’s another practical example. Let’s say you’re getting contact information from multiple sources and want to use the first available email address:

coalesce(
  variables('PRIMARY_EMAIL_FOR_USER'), 
  outputs('UserProfile')?['body/email'],
  variables('BACKUP_EMAIL'),
  'hi@manueltgomes.com'
)

This expression will:

1. First, try to use the value in the PRIMARY_EMAIL_FOR_USER variable
2. If that’s null, try to use the email from the Get User Profile Action action and see if it returns something.
3. If that’s also null, try to use the BACKUP_EMAIL variable
4. If all else fails, use the default email ‘hi@manueltgomes.com’

Real-world examples

Example 1: Handling optional form fields

Let’s say you have a set of inputs you want to check to build a personalized greeting.

Here’s a potential formula using a “concat” function and our “coalesce” function.

concat(
  'Hello, ',
  coalesce(
    triggerBody()?['text_1'], 
    triggerBody()?['text_2'], 
    'Guest'
  ),
  '!'
)

Here’s the result:

This will try to use the nickname first (first text field), then the “Name” (second text field), and finally fall back to “Guest” if both are missing.

Here’s what would happen if we don’t provide any values.

No need to build nested “if” statements where you would need to check if the value would be empty or not.

Example 2: Working with SharePoint data

When retrieving data from SharePoint using the “Get Item” action, for example, some columns might be empty. Let’s say we’re building an approval email and want to include the document owner:

coalesce(
  outputs('Get_item')?['body/Editor/Email'],
  outputs('Get_item')?['body/Author/Email'],
  'hi@manueltgomes.com'
)

Here’s the result:

This ensures we always have a valid email address for the approval process.

Example 3: Date handling

The “coalesce function” is also useful for date handling when some dates might not be set:

formatDateTime(
	  coalesce(
	    triggerBody()?['date_1'],
	    addDays(utcNow(), 7)
	  ),
	  'yyyy-MM-dd'
	)

In this case, we’re using the “formatDateTime” function. Here, we use the provided due date if available or default to 7 days from now if not provided.

Common Mistakes and Solutions

Here are some common issues people encounter when using the “coalesce function”:

Confusion with empty strings

One important thing to note is that the “coalesce” function only checks for null or undefined values. Empty strings (”) are considered valid, non-null values. This can confuse you when you expect a fallback value to be used but get an empty string instead. 

For example:

coalesce('', 'fallback')

This will return an empty string, not ‘fallback’, because an empty string is not null.

If you want to handle empty strings as well, you might need to combine with the “if” function as well as the “equals” function, for example :

if(
  equals(variables('TEST_VARIABLE'), ''),
  'Default Value',
  coalesce(variables('TEST_VARIABLE'), 'Default Value')
)

Not handling deeply nested properties correctly

When working with complex objects, the safe navigation operator (?) is important:

Incorrect:

coalesce(outputs('HTTP_Request')['body']['data']['user']['name'], 'Unknown User')

Correct:

coalesce(outputs('HTTP_Request')?['body/data/user/name'], 'Unknown User')

The second version will gracefully handle the case where any part of the path doesn’t exist.

Too many parameters

While the “coalesce function” can accept many parameters, limiting them to a reasonable number for readability is good practice. If you have too many fallback options, consider restructuring your logic. 

Limitations

There are a few things to be aware of when using the “coalesce function”:

Expression size limits

As with all Power Automate expressions, there’s a limit of 8,192 characters. You might hit this limit if you’re building complex coalesce expressions with many options. In such cases, consider breaking your logic into smaller parts using variables. 

No type checking

The “coalesce” function doesn’t perform any type checking or conversion. It simply returns the first non-null value, even if that value is of a different type than what you might expect. Remember this when working with numerical values or dates from other sources. This is especially important if you’re assigning the value to a variable, for example. 

Recommendations

Here are some things to keep in mind when using the “coalesce function”:

Use “debug” compose actions

Since the “coalesce function” silently selects values, it can sometimes be hard to understand which option was chosen. I recommend using Compose actions to debug your coalesce expressions. This way, you can see precisely which value was selected and why.

Consider default values carefully

When providing fallback values, think carefully about what makes sense for your Flow. Default values should be appropriate for the context and not cause issues in your Flow.

Always add a comment

Adding a comment will help avoid mistakes. Indicate why you are using coalesce and what the fallback values represent. It may look obvious initially, but it will not be in a few months or years. It’s essential to enable faster debugging when something goes wrong.

Use with conditional checks

Sometimes, you might want to take different actions based on whether you use the primary value or a fallback. In these cases, consider storing the result of coalesce in a variable first, then using conditional actions to handle different paths. 

Sources

Microsoft’s coalesce Function Reference

Back to the Power Automate Function Reference.

Photo by Thomas Yohei on Unsplash