FetchXML in Power Automate: When OData $filter Is Not Enough

I had 14 notification flows to build for a performance management system on Dataverse. The first flow needed to find evaluations assigned in the last 24 hours. I typed $filter=modifiedon ge and stopped. OData has no last-x-hours operator. Ten minutes into the project, I hit a wall.

That project was Meridian Performance Management at Apex Federal Solutions. 24 flows total, 14 of them notification flows following the NTF-EMAIL naming pattern. Every single notification flow ended up using FetchXML instead of OData $filter. Not by preference. By necessity. The notification architecture required daily digest queries with temporal lookbacks, linked entity joins for recipient resolution, and ordering for per-person email grouping. Every one of those requirements ruled out OData.

Where $filter Runs Out

OData $filter works well for simple queries. statuscode eq 1. name eq 'Contoso'. revenue gt 1000000. For single-table, single-condition filtering, it’s the right tool.

It stops being the right tool in four situations.

Temporal operators. “Find records modified in the last 24 hours.” OData has no last-x-hours. You’d need a Compose action to calculate addHours(utcNow(), -24), then string-interpolate the result into a $filter expression. It works, but it’s brittle and ugly. FetchXML gives you operator="last-x-hours" value="24" as a first-class operator.

Linked entity joins. You need an evaluator’s name, the evaluation form name, AND the reviewee’s full name, which lives two relationships away. OData $expand can follow one lookup. Nested $expand syntax like $expand=mrd_review($expand=mrd_personnel($select=mrd_fullname)) is inconsistently supported across Dataverse connector versions.

Multi-value IN filters. “Find evaluations in status A or status B or status C.” OData requires (status eq 'guid1' or status eq 'guid2' or status eq 'guid3'). FetchXML gives you operator="in" with child <value> elements. Cleaner, and scales to any number of values.

Ordering combined with complex filtering. OData supports $orderby, but when you combine it with multi-condition filters and expansions, the query string becomes unreadable. FetchXML keeps ordering, filtering, and joins in a structured XML hierarchy.

FetchXML Fundamentals in Power Automate

FetchXML queries go into the Fetch Xml Query field of the List rows action in the Microsoft Dataverse connector. You’ll find it under Advanced Options.

Every FetchXML query in a solution-aware flow should use connection references, not hardcoded connections. The List rows action itself references a Dataverse connection reference. The FetchXML inside it can contain dynamic expressions like @{utcNow()} that Power Automate evaluates at runtime.

Temporal Operators: The Killer Feature

This is where FetchXML earns its place. Dataverse has a full library of temporal operators that OData simply does not expose.

last-x-hours: Daily Digest Lookback

NTF-EMAIL-01 in Meridian sends a daily digest at 8:00 AM with all evaluations assigned in the previous 24 hours. The query:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="mrd_personnelevaluation">
    <attribute name="mrd_personnelevaluationid" />
    <attribute name="mrd_name" />
    <attribute name="mrd_evaluationduedate" />
    <attribute name="mrd_evaluator" />
    <filter type="and">
      <condition attribute="mrd_personnelevaluationstatus"
                 operator="eq"
                 value="a0fed756-9f0b-f111-8406-0022480b7cb8" />
      <condition attribute="mrd_evaluator" operator="not-null" />
      <condition attribute="modifiedon" operator="last-x-hours" value="24" />
    </filter>
    <order attribute="mrd_evaluator" />
  </entity>
</fetch>

Three conditions that would require three separate workarounds in OData. The status check, the null guard, and the temporal lookback. FetchXML handles all three in a single, readable block.

To do this in OData, you’d need a Compose action with addHours(utcNow(), -24), then reference that output inside the $filter string. It works, but now you have flow logic split across two actions instead of one self-contained query.

olderthan-x-days: Supervisor Escalation

NTF-EMAIL-12 escalates to supervisors when evaluations have been past due for more than 7 days. The olderthan-x-days operator finds records that have been sitting in an overdue state:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="mrd_personnelevaluation">
    <attribute name="mrd_personnelevaluationid" />
    <attribute name="mrd_name" />
    <attribute name="mrd_evaluationduedate" />
    <attribute name="mrd_evaluator" />
    <filter type="and">
      <condition attribute="mrd_personnelevaluationstatus"
                 operator="in">
        <value>62def52f-9f0b-f111-8406-0022480b7cb8</value>
        <value>dbd19548-9f0b-f111-8406-0022480b7cb8</value>
      </condition>
      <condition attribute="mrd_evaluationduedate"
                 operator="olderthan-x-days"
                 value="7" />
      <condition attribute="mrd_evaluator" operator="not-null" />
    </filter>
    <order attribute="mrd_evaluator" />
  </entity>
</fetch>

Notice the operator="in" with child <value> elements. This checks two statuses (past due and overdue) in a single condition. The OData equivalent would be (mrd_personnelevaluationstatus eq '62def52f-...' or mrd_personnelevaluationstatus eq 'dbd19548-...'). Now add the olderthan-x-days equivalent (which doesn’t exist natively) and you’re looking at a query string that nobody can debug.

on-or-before: Past Due Reminders

NTF-EMAIL-13 finds signing steps that are past due. The step must be in “Awaiting” status AND the due date must be on or before right now:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="mrd_evaluationsigningstep">
    <attribute name="mrd_evaluationsigningstepid" />
    <attribute name="mrd_duedate" />
    <attribute name="mrd_steporder" />
    <attribute name="mrd_signer" />
    <attribute name="mrd_evaluation" />
    <filter type="and">
      <condition attribute="mrd_stepstatus"
                 operator="eq"
                 value="691090001" />
      <condition attribute="mrd_duedate"
                 operator="on-or-before"
                 value="@{utcNow()}" />
    </filter>
    <link-entity name="mrd_personnelevaluation"
                 from="mrd_personnelevaluationid"
                 to="mrd_evaluation"
                 link-type="inner"
                 alias="eval">
      <attribute name="mrd_name" />
      <attribute name="mrd_evaluator" />
    </link-entity>
    <order attribute="mrd_signer" />
  </entity>
</fetch>

The @{utcNow()} expression is evaluated by Power Automate before the query runs. The on-or-before operator captures everything past due, regardless of whether it’s one day or one year overdue.

Linked Entity Joins: One Query Instead of Five

The notification for NTF-EMAIL-01 needs to include the evaluator’s name, the form name, and the reviewee’s full name. The reviewee lives on the mrd_personnel table, which connects through mrd_personnelreview. That’s two joins from the evaluation record.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="mrd_personnelevaluation">
    <attribute name="mrd_personnelevaluationid" />
    <attribute name="mrd_name" />
    <attribute name="mrd_evaluationduedate" />
    <attribute name="mrd_evaluator" />
    <link-entity name="mrd_personnelreview"
                 from="mrd_personnelreviewid"
                 to="mrd_review"
                 link-type="inner"
                 alias="review">
      <attribute name="mrd_name" />
      <link-entity name="mrd_personnel"
                   from="mrd_personnelid"
                   to="mrd_personnel"
                   link-type="inner"
                   alias="reviewee">
        <attribute name="mrd_fullname" />
      </link-entity>
    </link-entity>
    <filter type="and">
      <condition attribute="mrd_personnelevaluationstatus"
                 operator="eq"
                 value="a0fed756-9f0b-f111-8406-0022480b7cb8" />
      <condition attribute="modifiedon" operator="last-x-hours" value="24" />
    </filter>
    <order attribute="mrd_evaluator" />
  </entity>
</fetch>

All three tables in one query. The results come back flat with aliased columns: review.mrd_name for the review cycle name and reviewee.mrd_fullname for the person being reviewed. No secondary “Get a row by ID” actions. No nested Apply to each loops.

Without FetchXML, this flow would need a List rows to get evaluations, then a Get row for each evaluation’s review, then another Get row for each review’s personnel record. That’s 1 + N + N API calls. With FetchXML, it’s 1.

Dataverse supports up to 15 link-entity elements per query. In practice, I’ve never needed more than 3. If you’re hitting 15, your data model needs attention.

Ordering for Recipient Grouping

This pattern is non-obvious and saves hundreds of flow run actions.

Look at the <order attribute="mrd_evaluator" /> in every example above. That’s not for display. It’s the engine of the daily digest pattern.

The flow needs to send one email per evaluator containing all their assigned evaluations. Without ordering, you’d need to: get all evaluations, extract unique evaluator GUIDs, then loop through each GUID and filter the evaluations. That’s O(n^2) in Power Automate actions.

With ordering, the results arrive grouped by evaluator GUID. The flow iterates through the list once. When the evaluator GUID changes from the previous row, it knows: finalize the current email body, send it, start building the next one. One pass. O(n) actions.

In Meridian’s production environment, this reduced NTF-EMAIL-01 from approximately 180 actions per run (with nested loops for 30 evaluators) to 62 actions. Fewer actions means faster runs, lower API consumption, and less chance of hitting the 100,000-actions-per-day limit.

When Should You Use FetchXML Instead of OData?

If your Power Automate flow needs temporal operators like last-x-hours, joins across multiple Dataverse tables, or IN filters with several GUIDs, use FetchXML. OData $filter works for simple single-table queries, but production notification and reporting flows almost always hit at least one limitation that only FetchXML can solve.

Here’s the decision framework I follow after building those 14 notification flows.

Use OData $filter when:

• The query filters on a single table with simple conditions
• You need built-in pagination ($top / $skip)
• The query fits in one readable line
• You’re building a quick prototype and don’t need temporal logic

Use FetchXML when:

• You need temporal operators (last-x-hours, olderthan-x-days, on-or-before, last-week)
• You need joins across two or more related tables
• You need IN with multiple values, especially GUIDs
• You need ordering combined with complex filtering
• The query will be maintained by a team and structured XML is easier to read than a long filter string

In my experience, most production notification and reporting flows end up needing at least one of these FetchXML-only capabilities. I start prototyping with OData and switch to FetchXML the moment I hit a limitation. For Meridian, that moment came on flow number one.

Performance Tips

A few things I learned the hard way.

Select only the attributes you need. Never use all-attributes. Every extra column is data transferred from Dataverse to Power Automate. In a flow that runs daily across hundreds of records, this adds up.

Use exists or in link-types when you don’t need columns from the joined table. If you’re filtering by a related record’s status but don’t need any of its fields in the output, link-type="exists" is faster than link-type="inner" because it skips the join in the result set.

Avoid leading wildcards. like '%Smith' forces a table scan. like 'Smith%' can use an index. This applies in FetchXML the same way it applies in SQL.

Test your row counts. Remember that List rows with FetchXML caps at 5,000 rows without manual paging. If your notification flow could plausibly match 5,000+ records, either add tighter filters or implement paging with page and paging-cookie.

---

Spec-Driven Power Platform Series

This article is part of a series on building Power Automate solutions with specs, governance, and AI:

1. Tag-Based Flow Architecture - How 3-letter prefixes make 24 flows manageable
2. Spec-First Development - Why specs should exist before the designer opens
3. Notification Architecture - Notifications that cannot break business logic
4. FetchXML in Power Automate - When OData $filter is not enough
5. Building Solution ZIPs - The undocumented packaging guide
6. What AI Gets Wrong - And why human correction is the point
7. 14 Flows in 10 Minutes - The full story

---

Power Automate Governance - The Enterprise Playbook

This article is part of a 10-part series:

1. Naming Conventions That Scale
2. Environment Strategy - Dev Test Prod
3. Solution-Aware Flows
4. Flow Inventory
5. Pipelines - Dev to Prod
6. CoE Starter Kit
7. AI-Powered Flow Review
8. Versioning and Source Control
9. The Governance Repo
10. Weekly Governance Digest

---

AZ365.ai - Azure and AI insights for architects building on Microsoft. Follow Alex on LinkedIn for architecture deep dives.