The Power Apps coalesce function lives in the same family as Blank, IsBlank and IsEmpty. It’s a very efficient way to evaluate controls and outputs to return the first value that’s not blank. It can also help set default values if all analysed outputs are blank.
I’ve not covered a lesser-known Power Fx function in a while, so thought it was time for another. Coalesce is a function I regularly use now in Power Apps builds but it does seem to slip under the radar. There are lots of good use cases for using it that will streamline your Power Fx logic.
As per the Microsoft documentation here, coalesce is the more efficient way of the ‘Not Isblank’ combination. Instead of doing this:
If( Not IsBlank( value1 ), value1, Not IsBlank( value2 ), value2 )
We’ll look to do this:
Coalesce( value1, value2 )
Furthermore, the ‘If’ method will simply return a blank if there’s no ‘else’ specified. With coalesce, we can add a custom fallback:
Coalesce( value1, value2, "Fallback response" )
As ever, let’s use some Star Wars based examples to see how this can work in your apps.
Starting simple
We know the Power Apps coalesce function finds the first non-blank and non-empty value in a range. If the range is fully populated with outputs, the first output will be returned. This works with controls or hard-coded text.
Below, there are no blank entries so the function will return the first value it finds:
// Return the first non-blank value in range
Coalesce("Luke Skywalker","Darth Vader")
Next up, we can see the impact of using two double quotes to force a blank value. Coalesce will recognise this, returning the next non-blank value:
// Return the first non-blank value in range
Coalesce("","Luke Skywalker")
Finally, it also recognised the Blank function, so will skip over that to find the next non-blank value:
// Return the first non-blank value in range
Coalesce(Blank(),"","Luke Skywalker")
Common scenario
// If all fields in range are blank, return message
If(
Not(IsBlank(txtInput01.Text)),
txtInput01.Text,
Not(IsBlank(txtInput02.Text)),
txtInput02.Text,
Not(IsBlank(txtInput03.Text)),
txtInput03.Text,
Not(IsBlank(txtInput04.Text)),
txtInput04.Text,
"They're all blank"
)
The Power Apps coalesce function works better for this scenario:
// If all fields in range are blank, return message
Coalesce(
txtInput01.Text,
txtInput02.Text,
txtInput03.Text,
txtInput04.Text,
"They're all blank"
)
Both methods work in the same way and provide the same outputs. However, coalesce is a far more streamlined method. Here we can see the outputs change depending on how many preceding controls are blank:
Validating user details
Checking a logged-on users information is a very common practice in Power Apps. On occasion, the User function may return a blank match; I’ve covered using the Office 365 Users connector instead as it’s more accurate. If you are going to use the User function, it’s good to ensure you’re returning a result.
We might be inclined to determine if the User function returns a blank with the following logic:
// Is logged-on user's email address blank
If(
IsBlank(User().Email),
"No user identified",
User().Email
)
As you can see, we are specifying user email twice. The Power Apps coalesce function returns the same output but with less Power Fx:
// Is logged-on user's email address blank
Coalesce(
User().Email,
"No user identified"
)
Analysing the logged-on users profile using the Office 365 connector is performed in the same way. Perhaps you want to check the presence of the users department, manager, phone number or other properties. The If IsBlank approach…
// Is logged-on users department field populated
If(
IsBlank(Office365Users.MyProfileV2().department),
"No department specified",
Office365Users.MyProfileV2().department
)
… can again be replaced by using coalesce:
// Is logged-on users department field populated
Coalesce(
Office365Users.MyProfileV2().department,
"No department specified"
)
Handling blank lookups
In the example below, I’m using a dropdown control to select a Star Wars character. A lookup will run against the list of characters to see if the character exists.
We might run the following Power Fx to set a value if the lookup is blank. As per the previous examples, we’re having to specify the lookup twice:
// Find selected character or return default value
If(
IsBlank(
LookUp(
Characters,
Title = dpdCharacterSelect.Selected.Value
).Allegiance
),
"Unknown character",
LookUp(
Characters,
Title = dpdCharacterSelect.Selected.Value
).Allegiance
)
You could streamline using the With function, but the If function still has to run the same analysis and duplication:
// Find selected character or return default value
With(
{
tvLookup: LookUp(
Characters,
Title = dpdCharacterSelect.Selected.Value
).Allegiance
},
If(
IsBlank(tvLookup),
"Unknown character",
tvLookup
)
)
The Power Apps coalesce function provides a far cleaner alternative:
// Find selected character or return default value
Coalesce(
LookUp(
Characters,
Title = dpdCharacterSelect.Selected.Value
).Allegiance,
"Unknown character"
)
Gallery filtering fallback
You may have a requirement to filter a gallery by a specific output. If the filter returns zero results, default to showing other records rather than nothing.Â
To demonstrate, here we can see two galleries. Each must show all records where the Allegiance equals ‘Rebels’ if the dropdown control is blank:
When I first started building Power Apps, I may have tackled the scenario using If and CountRows:
If(
// Dynamically filter records based on dropdown selection
CountRows(
Filter(
SortByColumns(
Characters,
"Title",
SortOrder.Ascending
),
Allegiance = dpdAllegianceFilter.Selected.Value
)
) = 0,
// If dynamic filter returns zero records, default to showing all records for Rebels
Filter(
SortByColumns(
Characters,
"Title",
SortOrder.Ascending
),
Allegiance = "Rebels"
),
// Else show dynamic filtered records
Filter(
SortByColumns(
Characters,
"Title",
SortOrder.Ascending
),
Allegiance = dpdAllegianceFilter.Selected.Value
)
)
It could even be streamlined to some extent using the With function. I’ve seen this method used to get rid of delegation warnings (SPOILER ALERT – it only masks them, doesn’t get rid of them):
With(
{
// Store data as object
tvData: SortByColumns(
Characters,
"Title",
SortOrder.Ascending
)
},
If(
// Run initial dynamic Filter, count records
CountRows(
Filter(
tvData,
Allegiance = dpdAllegianceFilter.Selected.Value
)
) = 0,
// Default to Rebels if dynamic filter returns zero records
Filter(
tvData,
Allegiance = "Rebels"
),
// Else return records for dynamic filtering
Filter(
tvData,
Allegiance = dpdAllegianceFilter.Selected.Value
)
)
)
Again, see how the Power Apps coalesce function reduces the complexity and amount of Power Fx:
// Filter dynamically or show Rebels records by default
Filter(
SortByColumns(
Characters,
"Title",
SortOrder.Ascending
),
Allegiance = Coalesce(
dpdAllegianceFilter.Selected.Value,
"Rebels"
)
)
Patching new or existing record
Perhaps my favourite example of using coalesce. We can write Power Fx to determine whether a record exists in our data source. If it does, update the record, else add a new record. This is usually tackled with another If / IsBlank combo:
If(
!IsBlank(
LookUp(
Characters,
ID = cvSelectedItem.ID
)
),
// If not Blank, edit entry
Patch(
Characters,
LookUp(
Characters,
ID = cvSelectedItem.ID
),
{
Title: txtCharacterName.Text,
Allegiance: dpdCharacterAllegiance.Selected.Value
}
),
// Else, create entry
Patch(
Characters,
Defaults(Characters),
{
Title: txtCharacterName.Text,
Allegiance: dpdCharacterAllegiance.Selected.Value
}
)
)
The Power Apps coalesce function will mature the above Power Fx; If the lookup for the ID doesn’t return a value, use the fallback of Defaults(Data Source) to signal a new record:
Patch(
Characters,
// Determine if new or existing record
Coalesce(
LookUp(
Characters,
ID = cvSelectedItem.ID
),
Defaults(Characters)
),
{
Title: txtCharacterName.Text,
Allegiance: dpdCharacterAllegiance.Selected.Value
}
)
Hopefully the examples above give you some good insight to a powerful function. See if it can become part of your day-to-day Power Fx toolkit! If you’re currently an avid user of coalesce and I haven’t covered a good example you know of, please let me know in the comments.
Thanks for reading. If you liked this article and want to receive more helpful tips about the Power Platform, don’t forget to subscribe or follow me on socials 😊
Used this guide today and cut my Patch in half. You sir are a legend as always. Thank you for the great guides.
Thanks Gavin, always appreciate the feedback & glad to be of help!
I had no idea Coalesce was so powerful! I just subscribed and am looking forward to more great examples and explanations. I am particularly interested in handling delegation in Dataverse.
Another great article!