Power Automate: Use Of COALESCE Function

Hello Friends,

Welcome back with another post on Power Automate. Today, we will discuss about another amazing function provided by Microsoft. This function is "COALESCE". As we know that the most common scenario in Power Automate flows is to verify the NULL or BLANK values. Normally, we use IF condition with EQUALS twice. However, we can reduce it to once using COALESCE. Another benefit of using COALESCE is to return a predefined value in case, if the property is NULL. Let's start.

1. Click on "+ New flow" >> "Instant cloud flow" >> "Manually trigger a flow". Give flow name as "POC-CoalesceDemo".
2. Add "Compose" action and add below JSON-

1. [
   
     {
   
       "FirstName": "Manjul",
   
       "MiddleName": "Mayank",
   
       "LastName": "Singh"
   
     },
   
     {
   
       "FirstName": "Sachin",
   
       "LastName": "Jain"
   
     },
   
     {
   
       "FirstName": "Lokesh",
   
       "MiddleName": "",
   
       "LastName": "Saini"
   
     },
   
     {
   
       "FirstName": "Sarvesh",
   
       "MiddleName": null,
   
       "LastName": "Jain"
   
     }
   
   ]
   

3. 
4. Add another action "Parse JSON" to parse the output of Compose action. Give the output of Compose action as Content. For Schema, click on "Generate from sample" and paste the JSON string we have passed into Compose action. click on Done. It will create the schema.
5. 
6. Now, if you see the schema, you will find the MiddleName is taken as string type, however, in our case, it can be null as well, so we will update the schema
7. 
8. Now Save the flow and test it. It got executed successfully.
9. Now, we will apply a loop upon it and for each item, we will concatenate the First Name, Middle Name, Last Name to get the complete name.

1. concat(
   
   	items('Apply_to_each')?['FirstName'],
   
   	' ',
   
   	items('Apply_to_each')?['MiddleName'],
   
   	' ',
   
   	items('Apply_to_each')?['LastName']
   
   )
   

10. 
11. Let's save and test it.
12. 
13. If we see, the last 3 names have double spaces between first name & last name. Now, what we will do, we will apply a check for NULL or BLANK. Let's see.

1. concat(
   
   	items('Apply_to_each')?['FirstName'],
   
   	' ',
   
   	if(
   
   		or(
   
   			equals(items('Apply_to_each')?['MiddleName'],null),
   
   			equals(items('Apply_to_each')?['MiddleName'],'')
   
   		),
   
   		'',
   
   		concat(items('Apply_to_each')?['MiddleName'],' ')
   
   	),
   
   	items('Apply_to_each')?['LastName']
   
   )
   

14. 
15. As we can see, the output is not looking better. Now, we will see, if we use the COALESCE, what will happen.

1. concat(
   
   	items('Apply_to_each')?['FirstName'],
   
   	' ',
   
   	if(
   
   		equals(coalesce(items('Apply_to_each')?['MiddleName'],''),''),
   
   		'',
   
   		concat(items('Apply_to_each')?['MiddleName'],' ')
   
   	),
   
   	items('Apply_to_each')?['LastName']
   
   )
   

16. 
17. If you can see, the output still the same. So, what is the difference? If you see the code, the code becomes shorter. This makes a significance impact, when you have to apply nested validations.
18. Now, the other benefit is that, if you want to return a static (or default) value in case if the parameter is NULL, you can achieve the same as well

1. concat(
   
   	items('Apply_to_each')?['FirstName'],
   
   	' ',
   
   	coalesce(items('Apply_to_each')?['MiddleName'],'-NULL-'),
   
   	' ',
   
   	items('Apply_to_each')?['LastName']
   
   )
   

19. 
20. This result is quite important. If you see the result, Sachin Jain & Sarvesh Jain is added with the default value, while Lokesh Kumar not. Because, if we look back at the initial JSON, Lokesh Kumar has MiddleName as BLANK (means not null) and BLANK is also treated as a value therefore, it was not replaced with default value.
21. This scenario is useful where you need to return the default value as 0 or current date or false or Yes/No.
22. So, this is how, COALESCE works. We can leverage its benefits in many ways. COALESCE gives amazing benefits when used with SELECT action.
23. The overall outcome is that COALESCE can be used to validate if any property is NULL or not. If it is NULL, then you may either replace with blank of any default value.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !