Upsert Azure DevOps work items into Dataverse

The challenge

A scheduled flow copies Azure DevOps work items into a Dataverse staging table called Work Items. The table already holds 20 work items from earlier runs, and every run brings back a mix: work items it has never seen, work items that haven’t changed, and work items that have.

Add a new row would duplicate everything it has already seen. Upsert a row is the right action — it updates the row if it exists and adds it if it doesn’t — but it finds that row by Row ID, and Row ID must be a GUID. A work item ID like 483452 isn’t one.

Two of the rows already in the table:

The next run brings back three work items: one new, one exactly as it already is in the table, and one whose title has changed.

[
  { "AdoId": 100000, "Title": "Add dark mode toggle" },
  { "AdoId": 483452, "Title": "Fix login flow" },
  { "AdoId": 514002, "Title": "Resolve billing report discrepancy" }
]

After the run the table should hold 21 rows: 100000 added, 483452 left as it was, and 514002 carrying its new title — with no duplicates.

Before you start

Every action below can be pasted in directly: add the action, open its ··· menu › Peek code, and paste. Power Automate keeps the action’s position (and runAfter) from wherever you dropped it, so add each one in order and only paste over its type/inputs.

• Add a Compose action named Work items, then peek-code-paste this. It stands in for your Azure DevOps action’s output:

{
  "type": "Compose",
  "inputs": [
    { "AdoId": 100000, "Title": "Add dark mode toggle" },
    { "AdoId": 483452, "Title": "Fix login flow" },
    { "AdoId": 514002, "Title": "Resolve billing report discrepancy" }
  ]
}

• A Dataverse table named Work Items with two text columns: ADO ID and Title. Its primary key column, shown in the table designer as Work Items, holds the row’s GUID.
• The Dataverse connector is Premium, so the flow needs a Power Automate Premium license (or a license that includes Dataverse).
• In a real sync, point the steps at your Azure DevOps action’s output instead of the Compose, and use its own field names in place of AdoId and Title.

The solution

One Select builds a GUID for every work item. An Apply to each then upserts each row with that GUID as its Row ID.

1234

1. Work items: the Compose holding the incoming work items.
2. Select Row IDs builds a stable GUID for each work item.
3. Apply to each runs once per work item.
4. Upsert a row writes it, using that GUID as the Row ID.

How it works

• Upsert a row looks for a row whose primary key equals the Row ID. If it finds one, it updates it; if not, it adds a new row with that exact GUID as its primary key.
• formatNumber(AdoId, '000000000000') turns 483452 into 000000483452. The zeros are placeholders, so the result is always 12 digits.
• concat('00000000-0000-0000-0000-', ...) puts those 12 digits in the last group of an otherwise all-zero GUID: 00000000-0000-0000-0000-000000483452.
• The same input always gives the same output. Work item 483452 produces the same GUID on every run, so the existing row is updated instead of duplicated — whether its title changed (514002) or not (483452).
• A new ID produces a GUID no row has yet, so Upsert a row creates the row with that GUID (100000).

Variations

• More than one source: give each source its own first group, e.g. 00000001-0000-0000-0000- for one Azure DevOps organization and 00000002-0000-0000-0000- for another. The same work item ID from two organizations then lands in two different rows.
• The ID arrives as text: wrap it in int() first — formatNumber(int(item()?['AdoId']), '000000000000') — because formatNumber needs a number.
• Your Azure DevOps action’s output: swap outputs('Work_items') for that action’s output, and item()?['AdoId'] / item()?['Title'] for its own ID and title fields.

Gotchas

• Unchanged rows are still written. Upsert a row doesn’t compare values. In the screenshot above, 483452 came back with the title it already had, and its Modified On still jumped to 4:32 PM — so anything that fires on update (audit history, flows triggered by a modified row) fires too. To skip unchanged rows, filter them out before the Apply to each.
• Don’t use guid(). It’s random, so it never matches an existing row and every run adds duplicates.
• Pick the pattern once. Rows already in the table keep the GUIDs they were created with. Switching to a new pattern later makes every work item a new row, so reload the table if you change it.
• Mind the throttling limit. The Dataverse connector allows 6,000 calls per connection every 5 minutes, and each upsert is one call. For large loads, set the Apply to each concurrency with that limit in mind.
• The ID has to fit in 12 digits. Azure DevOps work item IDs are 32-bit integers — at most 10 digits — so they always fit.