“I heard about the Power Automate coalesce expression, what is it good for, can I use it to optimise my flows?”
Power Automate has many expressions, small pieces of code to add into flows as part of the process. By using them you can do various data operations, transformations, conversions, formatting, and much more without the need for an extra action. But you probably already know this as there’re some expressions you use on daily basis, e.g. expressions to format date.
However, there’re also a few expressions that can help you optimise the whole flow design. One of those that can help you simplify flows is the coalesce(…) expression.
What does coalesce(…) do?
By the definition of the expression, coalesce(…) will return the first non-null value. You input a list of values to check, let it be values, dynamic contents, or expressions, and it’ll return the first one that has a value. If there’s none with value it returns null.
For example:
coalesce(null, '1', null) = 1
coalesce(null, null) = null
How is that useful?
Since it’s returning the first value that is not null, the first use case that comes to mind is replacement for the expressions checking if something is empty:
if(empty(<valueA>), <valueB>, <valueA>)Since empty values = null, you can replace it with a coalesce(…) expression instead:
coalesce(<valueA>, <valueB>)And it’s not limited to only 2 values as the if(…) expression, you can evaluate as many values as you want to get the first non empty one.
coalesce(<valueA>, <valueB>, <valueC>, ...)A real world example
To use it in a real scenario, let us have a SharePoint list with 3 people picker columns, but only one of them will have a value – a user with an email address. How do you check in the flow which column it is and get the user email?
One way would be a bunch of if(…) expressions. If column1 is empty, check column2, if it’s empty take column3… if(…) expression inside another if(…) expression where it starts getting a bit complicated.
if(empty(<column1>),
if(empty(<column2>),
<column3email>,
<column2email>),
<column1email>)That’s a lot of expressions that can be easily replaced with a single coalesce(…):
coalesce(<column1>, <column2>, <column3>)
coalesce(outputs('Get_item')?['body/User1/Email'],outputs('Get_item')?['body/User2/Email'],outputs('Get_item')?['body/User3/Email'])
How can it optimise the flow design?
The flow optimising part happens once you start using the coalesce(…) expression not only to reference values, but to reference flow actions too!
As you probably already know, all Power Automate actions return JSON as their output (that is translated into the dynamic content). But they produce output only if the process executes these actions. If an action is skipped for any reason, it doesn’t return anything, the output is null. And coalesce(…) expression is skipping null values!
Use it to reduce duplicate actions
For example, if you have some branching in a flow, let it be a Condition, Switch, or parallel branches, you don’t have to keep all the logic in their respective branch. If you have an approval task A and another approval task B, you don’t have to evaluate the result in each branch, creating duplicate actions.
You can use a coalesce(…) expression instead to check the outcome of the action that was part of the process.
coalesce(<outcomeTask1>, <outcomeTask2>)
coalesce(outputs('Start_and_wait_for_an_approval')?['body/outcome'], outputs('Start_and_wait_for_an_approval_2')?['body/outcome'])
Or another example when creating various documents in a flow. You don’t have to create the file in each of the branches, you can have just one ‘Create file’ action with a coalesce(…) expression to create a file from the file content provided by any of the actions.
coalesce(body('Populate_a_Microsoft_Word_template_-_RFI'), body('Populate_a_Microsoft_Word_template_-_Proposal'), body('Populate_a_Microsoft_Word_template_-_Contract'))
…or to implement some “try and see” functionality
The same logic as when an action is skipped can be used in the “action has failed” too scenario. Failed actions return only the failure information in the JSON, not the data you’d expect, meaning you can use coalesce(…) also to see if an action failed.
For example, when copying files from user OneDrive, you need the exact location of the file and the file name. But what if you don’t have the full information? If you want to check a few folders and see if it’s there? Use coalesce(…)!
Try with a few actions, each running only if the previous one failed. At the end collect the output from the successful one, no matter which one it was. Just don’t forget to run the action with coalesce(…) also if the previous action is skipped.
coalesce(outputs('Copy_file')?['body/ItemId'], outputs('Copy_file_2')?['body/ItemId'], outputs('Copy_file_3')?['body/ItemId'])
Summary
As you can see, you can have a lots of fun with the Power Automate coalesce expression. It’s a useful replacement for the if something is empty use something else expressions, but where I find it even more valuable is the evaluation of previous actions.
The coalesce(…) expression can help you make the flow more compact as you don’t have to build each branch in the flow separately. Split it only when really needed instead and join it back with coalesce(…) into a straightforward flow, having all the shared functionality only once, no matter which side branch it went before.