#PowerPlatformTip 136: 'Patch Coalesce Upsert'

TL;DR: Do upsert in Power Apps with one Patch(Source, Coalesce(LookUp(...), Defaults(Source)), {...}), update or create in a single call.

💡 Challenge

In Power Apps, keeping separate logic branches for “Create” and “Update” bloats your formulas and invites bugs. How can you fold this into a single operation?

✅ Solution

Use a single Patch() call combined with Coalesce(). If LookUp() finds no record, Coalesce() falls back to Defaults(DataSource), so the same call either updates an existing record or creates a new one.

🔧 How It’s Done

1. Define your data source

🔸 Make sure you have a table or collection connection, e.g. Contacts.

2. Retrieve any existing record

🔸 varRecord = LookUp(Contacts, Email = varEmail), returns the record if it exists, otherwise Blank.

3. Patch with Coalesce

🔸 Combine Patch() and Coalesce():

Patch(
    Contacts,
    Coalesce(
        varRecord,
        Defaults(Contacts)
    ),
    {
        Email: varEmail,
        FullName: txtName.Text,
        Phone: txtPhone.Text
    }
)

🔸 Coalesce(varRecord, Defaults(Contacts)) uses the Defaults template when varRecord is Blank, creating a new record.

4. Publish and test

🔸 If a matching record exists it’s updated; otherwise a new record is created with your field values.

🎉 Result

You’ve consolidated two code paths into one: cleaner form logic, easier to maintain and less error-prone. “Upsert” (update or insert) now happens seamlessly with a single Patch().

🌟 Key Advantages

🔸 One unified call instead of separate Create/Update branches

🔸 No extra variables or If() checks needed

🔸 Cleaner maintenance and improved performance

🔸 Consistent behavior across SharePoint, Dataverse, or any Power Apps data source

🔍 What Coalesce Does with Blank and Empty Text

Coalesce returns its first argument that is not blank. Empty text counts as blank, whitespace does not:

🔸 Coalesce("", "fallback") and Coalesce(Blank(), "fallback") both return "fallback".

🔸 Coalesce(" ", "fallback") returns " ", three spaces are a value.

🔸 Coalesce("", "", "third") returns "third"; Coalesce(Blank(), Blank()) returns Blank().

🔸 Coalesce(0, 5) returns 0, zero is not blank.

🔸 With records: LookUp(Table({ID:1}), ID = 99) is Blank(), and Coalesce(thatResult, {ID:0, Name:""}) returns the fallback record.

🗝️ Why the Fallback Record Matters

Tested on a Dataverse Accounts table:

🔸 Patch(Accounts, LookUp(Accounts, 'Account Number' = "ZZ-NOPE"), {'Account Name': "x"}) with no matching record returns Blank() and writes nothing. A blank base record is not a create, it is a no-op. That is the gap Coalesce(..., Defaults(...)) closes.

🔸 Patch(Accounts, LookUp(Accounts, 'Account Number' = "ZZ-C"), {'Account Name': "ZZ tip test C7"}) with a match returns the updated record, .'Account Name' is "ZZ tip test C7" afterwards.

Known ID instead of LookUp

When you already hold the primary key, a record with just the key works as the base record and saves the LookUp:

Patch(Accounts, {Account: GUID("66bb188a-...")}, {'Account Name': "ZZ tip test C10"})

This updated the account with that ID. Coalesce(LookUp(Accounts, 'Account Number' = "ZZ-NOPE"), {Account: GUID("66bb188a-...")}) falls back to the key record and the subsequent Patch updated the same row.

🛠️ FAQ

1. Do I still need If() conditions to check for existing records? Coalesce() handles the check for you. If LookUp() returns Blank, it automatically uses Defaults(), so If() is unnecessary.

2. Will this approach work with Dataverse tables? Yes. Both Patch() and Coalesce() behave identically in Dataverse. Just replace Contacts with your table name (e.g. Accounts).

3. What if LookUp() returns more than one record? Make your lookup criteria unique (for example, a primary key or unique field). Otherwise LookUp() returns the first match and may lead to unexpected updates.

4. Does Coalesce treat an empty text box as blank? Yes. Coalesce("", "fallback") returns "fallback", so Coalesce(txtPhone.Text, "n/a") works for an empty input. Whitespace-only text is kept, and 0 is kept.

5. What happens if I Patch with a blank base record and no Coalesce? Nothing is written and Patch returns Blank(). The record is neither updated nor created, which is why the Defaults() fallback is needed for the create path.

6. Can I skip the LookUp when I know the ID? Yes. Patch(Source, {ID: knownId}, {...}) with a record that holds only the primary key updates that record directly, no LookUp call needed.

🔗 Related Tips

• #PowerPlatformTip 131: ForAll & Patch Optimization in PowerApps, batch bulk updates efficiently.
• #PowerPlatformTip 57: Mastering Patch, the core Patch patterns.