Unanswered

Assistance with List Rows and Upsert a Row for Matching and Non-Matching Rows in Dataverse

(1) Share

Report

Posted on by DA-27111458-0

Hello Power Automate Community! I'm trying to create a flow in Power Automate that processes rows from an Excel file, compares them to existing rows in a Dataverse table, and performs the following actions:

Update Matching Rows: If a row from the Excel file matches an existing row in Dataverse (based on unique identifiers), update the matching Dataverse row with new data from Excel.
Add Non-Matching Rows: If no matching row is found in Dataverse, create a new row using the data from Excel.

This is currently being done using the List Rows action to identify matching rows in Dataverse and the Upsert a Row action to update or add rows accordingly.

Issues Encountered

Duplicate Records: Despite efforts to identify and update matching rows, the Upsert a Row action creates duplicate records of matching rows instead of updating the existing rows.
Invalid OData Filter Query: Multiple attempts to write a valid OData filter query in the List Rows action have failed. The query is intended to compare a combination of fields (e.g., ChildsName and FileNumber) to identify matching rows.
Row ID in Upsert: The current Row ID expression in the Upsert action does not correctly reference the matching row's ID from Dataverse.

Attempted Solutions

Here are the OData filter query expressions and configurations I've tried for the List Rows action, all of which have failed:

ppts_childsname eq 'items('Apply_to_each_1')?['ChildsName']' and ppts_file eq 'items('Apply_to_each_1')?['FileNumber']' (Direct Dynamic Content Embedding)

concat("ppts_childsname eq '", items('Apply_to_each_1')?['ChildsName'], "' and ppts_file eq '", items('Apply_to_each_1')?['FileNumber'], "'") (Using concat() to Build the Query)

coalesce(items('Apply_to_each_1')?['ppts_AttendanceTrackerId'], guid()) (Row ID Expression in Upsert a Row) - This expression generated the following error - Action 'List_rows' failed: The $filter expression must evaluate to a single boolean value

Flow Configuration Overview

Trigger: The flow is triggered by a new file upload.
Apply to Each 1: Iterates through rows in the Excel file.
List Rows (Inside the loop): Used to retrieve matching records in Dataverse using an OData filter query.
Upsert a Row (Inside the loop): Updates any existing rows and/or adds new rows based on the List Rows results.

The excel file has both conditions within it (i.e. matching rows in Dataverse and non-matching rows) so I need the flow to be able to execute both either simultaneously or sequentially for multiple rows.

Key Questions for the Community

What is the correct OData filter query for the List Rows action to compare two fields (e.g., ChildsName and FileNumber) to identify matching rows in Dataverse?
1. ChildsName and FileNumber are the Excel column names
2. If Dataverse Schema Names are needed - ppts_ChildsName and ppts_File
3. If Dataverse Logical Names are needed - ppts_childsname and ppts_file
How should the Upsert a Row action be configured to:
- Use the ID of a matching row from List Rows for updates.
- Add a new row for non-matching records.
Should the List Rows action remain inside the loop, or is it more efficient to place it outside and reference its outputs?

Additional Context

Dataverse Table: AttendanceTracker
Key Fields:
- ChildsName (Text field)
- FileNumber (Text field, used as part of the unique identifier)
- AttendanceTrackerId (Unique ID for each record in Dataverse)
Excel Rows: Have been cleaned and filtered before processing in the flow.

Any guidance on constructing the correct List Rows OData filter query and configuring the Upsert a Row action to avoid duplicates and achieve the desired behavior would be greatly appreciated. These are the last 2 steps in the flow to get the entire flow working so hoping someone has the answer.

Thank you!

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (0)

Mark Nanneman 991 Super User 2025 Season 2 on at

Like (0)

Report
1. Odata Filter

Don't use the concat method to write an Odata filter.

Use one that looks like this:

ppts_childsname eq '@{items('Apply_to_each_1')?['ChildsName']}' and ppts_file eq '@{items('Apply_to_each_1')?['FileNumber']}'

2. Upsert Row ID

The expression code you give for your Upsert row ID is not correct, at least given the way you've described your flow. I believe this is the major error causing your flow to create duplicate records. The code you gave shows a reference to 'ppts_AttendanceTrackerId' from the "Apply to Each 1" loop, but your "Apply to Each 1" loop is supposed to be looping through excel rows, not your dataverse rows. The way you wrote it, "?['ppts_AttendanceTrackerId']" will always be null in your Excel loop, and so it will always default to the new guid()

coalesce(items('Apply_to_each_1')?['ppts_AttendanceTrackerId'], guid())

For an Upsert action like this, you'd probably want to grab your ID property from the output of your Dataverse List Rows action.

It would look something like

first(outputs('List_rows')?['body/value'])?['ppts_AttendanceTrackerId']

This will take the first item in the array returned by your List Items and grab its ppts_AttendanceTrackerId field.

Here's a blog post I have on using Upsert similar to what you're doing here.
https://marknanneman.wordpress.com/2024/12/11/best-practices-simplify-your-power-automate-dataverse-flows-with-upsert/

Try fixing your Upsert Row ID expression, and if it still fails send some screenshots!

Was this reply helpful? Yes No
DA-27111458-0 8 on at

Like (0)

Report

Thank you Mark or your suggestion. I tried using the suggested List Rows OData filter query however, I have found that Power Automate doesn't seem to like the "at" symbol in expressions and consistently returns invalid expressions. I'm not savvy with coding and I've been using Copilot to help me navigate building this flow which has been helpful to a point. Any tips for constructing a valid query that compares the Childsname and FileNumber combination columns from an excel file with matching rows in Dataverse would be truly appreciated.

In terms of the upsert a row step. The suggested expression of first(outputs('List_rows')?['body/value'])?['ppts_AttendanceTrackerId'] worked as written and only updated the first record/row in Dataverse. What I would like to see happen is that all rows in Dataverse that have a matched Childsname and FileNumber, all matching rows are updated. Additionally, any non-matching rows from the uploaded file, a new row is created. I've been having trouble constructing an expression that will do this effectively.

Do you have a suggestion of what the expression should look like?

Thank you so much for your assistance.

Was this reply helpful? Yes No