Unanswered

Assistance with List Rows OData Query in Dataverse Flow for Matching and Non-Matching Rows

(0) Share

Report

Posted on by DA-27111458-0

Hi Power Automate Community,

I’m working on a flow in Power Automate for attendance tracking in a pediatric therapy CRM built on Dataverse. The flow is designed to process matching rows and non-matching rows sequentially so that every row from an uploaded Excel file is either updated in Dataverse (if matching) or added as a new record (if non-matching). Each Excel file has the potential to include both matching and non-matching rows, so the flow must process both conditions dynamically and efficiently.

The current challenge is dynamically filtering rows in the List Rows action using an OData query to identify matching rows in the Dataverse table based on the combination of Child’s Name and File Number.

Steps Taken So Far:

1. Dynamic OData Query Construction

The goal is to dynamically construct an OData query to filter rows in Dataverse using values from the Excel file.

I’ve tried various dynamic expressions for constructing the query with the following two being the most recent, but both result in the following output in the List Rows action:

{
"value": []
}

Expression 1 (with 0):

concat('ppts_ChildsName eq ''', outputs('Compose_ChildsName')?[0], ''' and ppts_File eq ''', outputs('Compose_FileNumber')?[0], '''')

Expression 2 (without 0):

concat('ppts_ChildsName eq ''', outputs('Compose_ChildsName'), ''' and ppts_File eq ''', outputs('Compose_FileNumber'), '''')

Both expressions aim to dynamically reference the Child's Name and File Number values, but neither has worked so far.

2. Compose Steps for Dynamic Values

To prepare dynamic inputs for the OData query, I created Compose Child's Name and Compose File Number steps. These steps reference the output of a previous action (Select Clean Rows) to extract the required data.

Output from Select Clean Rows

The current expressions used are:

For Compose Child's Name:

outputs('Select_Clean_Rows')?[0]?['ChildsName']

For Compose File Number:

outputs('Select_Clean_Rows')?[0]?['FileNumber']

The outputs of these compose steps render the following error message: Action 'Compose_ChildsName' failed: Unable to process template language expressions in action 'Compose_ChildsName' inputs at line '0' and column '0': 'The template language expression 'outputs('Select_Clean_Rows')?[0]?['Combined'] ' cannot be evaluated because property '0' cannot be selected. Object properties can only be selected by names. Please see https://aka.ms/logicexpressions for usage details.'.

When the [0] is removed from the expression to process all rows, the compose output is blank causing the List Rows action to not produce the expected output, which are the existing records in Dataverse. Instead the output is:

{
"value": []
}

3. Purpose and Flow Requirements

The purpose of this flow is to process each row in the uploaded Excel file and perform the following:

Identify matching rows in Dataverse using the List Rows action with a dynamic OData query.
Update matching rows in Dataverse.
Add non-matching rows as new records in Dataverse.

Since each uploaded Excel file has the potential to contain both matching and non-matching rows, the flow must dynamically handle both conditions sequentially, ensuring all rows are processed.

Current Issues:

Compose steps are not generating the expected outputs causing the List Rows action to also not produce the necessary outputs.
Handling Matching and Non-Matching Rows: The flow must process both conditions (matching and non-matching) within the same file sequentially, but the current configuration struggles to achieve this.

Questions:

How can I properly structure the compose step expressions to output a properly formatted ChildName and FileNumber that is compatible with what Dataverse expects?
How can I properly structure the dynamic expressions to format the OData query correctly for the List Rows action to render the expected outputs?
How can I configure the flow to dynamically process all rows in the uploaded Excel file so that both matching and non-matching rows are processed correctly?
1. Matching rows - Update the existing record in Dataverse
2. Non-Matching rows - Add a new row/record
Are there alternative approaches to handling matching and non-matching rows that would simplify this process while maintaining efficiency?

Any guidance, suggestions, or examples would be greatly appreciated. If needed, I can provide additional details or screenshots for further clarification.

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

Where is your Dataverse List Rows action in your flow? Is it in a loop of some sort?

Is there any reason why you're constructing your odata filter with Concat()? It makes the filter logic much harder to read.

If I'm following your desired outcome correctly, I think you could simply your flow a lot.

1. List your excel rows

2. Loop through the excel rows array (or some Select output) and check for an existing record in Dataverse using a List Rows action and an Odata filter to check for a match based on a Unique Key or Id of some sort, or some combination of values that would constitute a unique match, like First Name, Middle Name, Last Name matches, or an email match or something.

3. Then add a new row or update existing as needed.

You might want to read up on the Dataverse Upsert action in Power Automate. It's a great way to simply flows that are either updating an existing row or adding a new one. There are a number of blogs on it, here's one I wrote:

https://marknanneman.wordpress.com/2024/12/11/best-practices-simplify-your-power-automate-dataverse-flows-with-upsert/

If this helped you, please click "Does this answer your question" and give it a like to help others in the community (+ close the ticket)!

Power Platform Developer | LinkedIn: Mark Nanneman | Blog: Power Stuff | YouTube: Mark's Power Stuff |

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

Like (0)

Report
Thank you Mark for your suggestion and the blog post reference—it’s been helpful to understand the Upsert action better. I’d like to address your questions and explain where I’m currently stuck.

Where is your Dataverse List Rows action in your flow? Is it in a loop of some sort?

The List Rows action is currently inside an Apply to Each loop that iterates through Excel rows. The intent is to check each Excel row against the existing Dataverse records. However, configuring the OData filter query in the List Rows step to dynamically match fields (e.g., Child's Name and File Number) has been challenging.

Is there any reason why you're constructing your OData filter with Concat()? It makes the filter logic much harder to read.

The use of Concat() and similar expressions was initially suggested to dynamically reference fields from the Excel file within the OData filter query. However, this approach has consistently failed due to syntax errors, symbols not being recognized in Power Automate, or mismatched field references. I agree that it’s making things overly complicated, which is why I’m trying to simplify this step and others.

The Current Problem:

Matching Excel Rows to Dataverse Rows Dynamically:

I need to compare all rows in Dataverse against all rows in the Excel file to determine whether to update an existing record or create a new one. This must be done based on a unique combination of fields (e.g., Child's Name and File Number).

Challenges with the OData Filter Query:

Attempts to dynamically reference Excel data fields in the filter query have consistently resulted in invalid expressions. This has prevented the List Rows action from functioning as intended. The following are additional OData expressions attempted using Dataverse logical names and Excel column Names:

ppts_childsname eq 'items('Apply_to_each_1')['ChildsName']' and ppts_file eq 'items('Apply_to_each_1')['FileNumber']'

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

Upsert Creates Blank Rows:

When I tested this step without the List Rows filter query, the flow created new rows in Dataverse, but they were blank, and an existing row was removed. I'm sure its a configuration issue but not sure how to fix this step. Current setup in the screenshot.

Each field that's being mapped to Dataverse has the following expression: items('Apply_to_each_1)?['FieldName']

Questions for Clarification:

Simplifying the Flow:

Can I use the Upsert action alone without List Rows, or is the List Rows step essential for ensuring no duplicates are created in Dataverse?

OData Filter Query Syntax:

How should the OData filter query be structured to dynamically match Excel row fields (e.g., Child's Name and File Number) with Dataverse schema fields? Should I use Dataverse logical names or schema names in the query? Can you provide an example?

Alternative Solutions:

Would you suggest any alternative approaches to dynamically check and match Excel rows against Dataverse records? I’ve seen suggestions to use Filter Array, but I’d like to minimize additional complexity if possible.

I appreciate any guidance or examples you can provide to help me move forward with this flow. Thank you!

Was this reply helpful? Yes No