Power Automate - Why You Should Love Coalesce

#powerautomate #logicapps #powerplatform #lowcode

One of my early blogs I spoke about how I love Do Unitil loops, and Im sure some people thought I was a little crazy, and now I'm talking about loving a expression, and I think everyone now knows Im crazy. But trust me, after a read you might be joining me on the coalesce side 😎

What is coalesce
Simple uses
Cool uses

1. What is coalesce

Well in a nutshell coalesce is designed to handle null/undefined values. So if you have a optional field in a action response you can use coalesce as a kind of if statement:

If value-is-not-null
  use-value
else
  use-this-value-instead

You can also stack more then one value, so it could be:

If value-is-not-null
  use-value
else If value-is-not-null
  use-value
else If value-is-not-null
  use-value
else If value-is-not-null
  use-value
else
  use-this-value-instead

The key things to remember is it will run sequentially, so the first valid will be returned, and if there is no match the last one will be returned (so always make the last is valid).

Coalesce also requires one type, so you cant have the first value a integer and the second a string, they would all have to be the same.

The last thing to call out is this is not a replacement for empty(), as blank strings and empty arrays will be classified as valid.

Input	Coalesce	Empty
undefined	true	true
null	true	true
"" (empty string)	false	true
{}	false	true
[]	false	true

Full MS Docs here: https://learn.microsoft.com/en-us/azure/logic-apps/expression-functions-reference#coalesc

2. Simple uses

The out of the box use for coalesce is when you are working with unstructured/inconsistent data. Actions will often have optional fields, which will mean if you reference those fiels you get a null value. Null values could break the action, or show unpleasant value to the end user.

So in the below example, how would you get the powerAutomate year for all users, when not all users have the certificate?

"value":[
  "user":{
    "name":"David",
    "location":"Nottingham",
    "certificates":{
      "powerAutomate":{"year":"2020"},
      "powerApps":{"year":"2022"},
      "copiltoStudio":{"year":"2024"}
    }
  }
],
[
  "user":{
    "name":"John",
    "location":"Timbuktu",
    "certificates":{
      "powerApps":{"year":"2024"},
      "copiltoStudio":{"year":"2024"}
    }
  }
]

This is where you would use a coalesce:

coalesce(
  items("For_each_User")?['user']?['certificates']?['powerApps']?['year']
,
  'Has Not Completed Certificate'
)

Likewise this can also be used for manual flows with optional inputs.

You could do it with a empty() expression and some if()s, like this:

if(
  empty(items("For_each_User")?['user']?['certificates']?['powerApps']?['year'])
,
  'Has Not Complete Certificate'
,
  items("For_each_User")?['user']?['certificates']?['powerApps']?['year']
)

But doing nested if()'s would be a lot more complicated, compared to a coalesce():

coalesce(
  items("For_each_User")?['user']?['certificates']?['powerApps']?['year']
,
  items("For_each_User")?['user']?['certificates']?['powerAutomate']?['year']
,
  items("For_each_User")?['user']?['certificates']?['copilotStudio']?['year']
,
  'Has Not Completed Certificate'
)

3. Cool uses

We will all agree, that although super useful, the above scenarios are not that common, so why would I love coalesce, well its because of another cool way to use it.

If you have read some of my previous blogs I want my flows to be optimised and efficient, no unnecessary actions (I deep dive more into it here The Direct Methodology). And coalesce will be your best friend for removing unnecessary actions, and that's because it's brilliant for conditions and switches.

Here's an example I see often:

The output from either branch is needed after, so its stored in a variable, makes sense right. But with a coalesce you don't need that variable, simple use the outputs where you would normally use the variable

coalesce(
  outputs('Get_a_row_by_ID')?['body/fullname']
,
  outputs('Get_a_row_by_ID_2')?['body/fullname']
)

And because you can have multiple inputs this can also be used on a switch.

There are some challenging situations, but again these can be done with a little creativity.

First Item
First is great when you want to grab just one row from an array (if only there was a real lookup expression), but that can break the coalesce, as the first throws an error not a null because a null has no length. But you can fix it like this

coalesce(
  first(
    coalesce(
      outputs('Get_rows')?['body/value']
    ,
      createArray()
    )
  )
,
  {"fallback":"hello world"}
)

So we first check if the array is null, if it is then we create empty array. Then we check to see if first item is null, if it is we fallback.

Array or Object
What happens if we wanted if a Get_item or Get_items, we know coalesce has to have the same type so it shouldn't work. Well again a little bit of creativity we can do it.

coalesce(
  outputs('Get_rows')?['body/value']
,
  array(outputs('Get_a_row')?['body'])
)

The reason this works is because our conditon means we always know either the Get_rows or Get_a_row will return a value. As the create array() will throw an error if Get_a_row is null, we do that second, ensuring it is never null.

As you can see coalesce has the power to remove multiple actions, a flow easier to read and saving API calls (remember a standard license only has 6k API per day and every call has a carbon footprint).

And that's why I love it, and there are over 140 expressions in total, so when building your next flow, try an expression before an action 😎.

I did a list of my favourite expressions to give you a head start.

Also I created a quick reference page with all the expressions and a Excel file you can download here Power DevBox Expressions

😎 Subscribe to David Wyatt