Microsoft Dataverse

Don’t Update or Insert records but Upsert in Dataverse when using Power Automate

A typical pattern for data import processes is that you first check if an item exists before either inserting or updating the found item. Upsert (Update or Insert) the item in one operation is however better.

Update or Insert

So the base process could look something like this. We first get the flow to check if the item already exists and then when it doesn’t exist we can add the item. However there is a bit of time between those two steps and things may have changed.

In the above example I would every now and then get failures in my high volume flow.

A record with matching key values already exists.

So how do we make this flow run more reliably?

Upsert action

Before you look for an upsert a row action in Power Automate, this doesn’t exist. Well actually it does exist under a different name.

In my solution I’m reading call records using Microsoft Graph in an HTTP request. Using the data returned I’m now doing an update a row using the id that I’ve been given by my Graph API call.

It is important to realize that the id used in the Row ID is a unique and in the standard GUID format. The external system (Call Records) is responsible for delivering unique guids to me and my Dataverse tables will inherit these unique ids.

The Update a row action will now look at my table and find the matching record to the id supplied. If this record exists we get the expected update of the record. If however the record doesn’t yet exist, a new record with that GUID is created.

Why not use Dataflows?

Well, that is a good question. For simple data imports, I would probably use dataflows. However for complex data where my data needs to be moved across multiple lists and potentially the data needs to be adjusted on the way, Power Automate may give the more flexible solution.

Is this method 100% bullet proof?

So far I’ve found that this is not 100% bullet proof but an awful lot better than the method mentioned before.

The error details that I got about once a day or so is shown below:

  "error": {
    "code": "0x80040237",
    "message": "A record with matching key values already exists.",
    "@Microsoft.PowerApps.CDS.ErrorDetails.SqlExceptionMessage": "Violation of PRIMARY KEY constraint 'PK_new_CallRecordBase'. Cannot insert duplicate key in object 'dbo.new_CallRecordBase'. The duplicate key value is <redacted>.",
    "@Microsoft.PowerApps.CDS.HelpLink": "",
    "@Microsoft.PowerApps.CDS.InnerError.Message": "Cannot insert duplicate key."

The above could happen if two flows try to attempt to add the same record at exactly the same time. #Exactly the same time hardly happens in the world of computers so you might never see the above.

Pieter Veenstra

Business Applications Microsoft MVP working as a Principal Architect at HybrIT Services Ltd. You can contact me using

Recent Posts

1 Step to create the REPT function in Power Automate

Today I was asked on the chat about how to create a REPT function in…

2 weeks ago

4 avoidable pitfalls of Low Code development?

Low Code Development is all about anybody can do it! But what are the pitfalls…

4 weeks ago

Implement booking availability in Power Apps in less than 5 minutes

Today I was asked how to implement a booking availability solution in Power Apps. The…

3 months ago

Delegation with SharePoint lists Power Apps vs Power Automate

We all know about delegation warnings in Power Apps, but how about delegation with SharePoint…

3 months ago

Timing issues in relation to App OnStart, Screen OnVisible, Timers and user interruptions in Power Apps

When you create apps, it is important to understand the timing issues that may happen.…

3 months ago

Implement a PDF preview in a Model Driven Apps

Recently I needed to implement a PDF preview in a Model Driven App. This should…

3 months ago