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

OData $filter is the default tool for filtering rows in a Power Automate List rows action. For simple single-table queries it is the right tool. For the kind of queries notification and digest flows actually need, it runs out of capability fast.

Three failure modes show up over and over in production flows: temporal filters with no native operator, joins across more than one related table, and IN-style filters across multiple GUIDs. FetchXML handles all three cleanly. This article walks the patterns and where each one is worth reaching for.

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 is 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 would need a Compose action to calculate addHours(utcNow(), -24), then string-interpolate the result into a $filter expression. It works, but it is brittle and ugly. FetchXML gives you operator="last-x-hours" value="24" as a first-class operator, documented in the FetchXML condition operators reference.

Linked entity joins. You need an evaluator’s name, a form name, and a reviewee’s full name that lives two relationships away. OData $expand can follow one lookup. Nested $expand syntax across multiple levels is inconsistently supported across Dataverse connector versions.

Multi-value IN filters. “Find records 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 it 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, 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 exposes a full library of temporal condition operators that OData does not surface.

last-x-hours: Daily Digest Lookback

Consider a daily digest scheduled at 8:00 AM that needs all records assigned in the previous 24 hours. The query against an app_personnelevaluation table looks like this:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="app_personnelevaluation">
    <attribute name="app_personnelevaluationid" />
    <attribute name="app_name" />
    <attribute name="app_evaluationduedate" />
    <attribute name="app_evaluator" />
    <filter type="and">
      <condition attribute="app_personnelevaluationstatus"
                 operator="eq"
                 value="00000000-0000-0000-0000-000000000000" />
      <condition attribute="app_evaluator" operator="not-null" />
      <condition attribute="modifiedon" operator="last-x-hours" value="24" />
    </filter>
    <order attribute="app_evaluator" />
  </entity>
</fetch>

Three conditions that would each require a workaround in OData: the status check is fine, but the null guard and the temporal lookback are not. FetchXML handles all three in a single readable block.

To do this in OData you would 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: Past Due Escalation

A supervisor escalation flow needs records that 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="app_personnelevaluation">
    <attribute name="app_personnelevaluationid" />
    <attribute name="app_name" />
    <attribute name="app_evaluationduedate" />
    <attribute name="app_evaluator" />
    <filter type="and">
      <condition attribute="app_personnelevaluationstatus"
                 operator="in">
        <value>00000000-0000-0000-0000-000000000001</value>
        <value>00000000-0000-0000-0000-000000000002</value>
      </condition>
      <condition attribute="app_evaluationduedate"
                 operator="olderthan-x-days"
                 value="7" />
      <condition attribute="app_evaluator" operator="not-null" />
    </filter>
    <order attribute="app_evaluator" />
  </entity>
</fetch>

GUIDs are illustrative placeholders. 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 (app_personnelevaluationstatus eq 'guid1' or app_personnelevaluationstatus eq 'guid2'). Add the olderthan-x-days equivalent (which does not exist natively) and you are looking at a query string nobody can debug.

on-or-before: Past Due Reminders

A signing-step reminder flow finds 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="app_evaluationsigningstep">
    <attribute name="app_evaluationsigningstepid" />
    <attribute name="app_duedate" />
    <attribute name="app_steporder" />
    <attribute name="app_signer" />
    <attribute name="app_evaluation" />
    <filter type="and">
      <condition attribute="app_stepstatus"
                 operator="eq"
                 value="691090001" />
      <condition attribute="app_duedate"
                 operator="on-or-before"
                 value="@{utcNow()}" />
    </filter>
    <link-entity name="app_personnelevaluation"
                 from="app_personnelevaluationid"
                 to="app_evaluation"
                 link-type="inner"
                 alias="eval">
      <attribute name="app_name" />
      <attribute name="app_evaluator" />
    </link-entity>
    <order attribute="app_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 is one day or one year overdue.

Linked Entity Joins: One Query Instead of Five

A daily digest notification often needs more than the primary record. Suppose the email body needs the evaluator’s name, the form name, and the reviewee’s full name. The reviewee lives on the app_personnel table, which connects through app_personnelreview. That is two joins from the evaluation record.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="app_personnelevaluation">
    <attribute name="app_personnelevaluationid" />
    <attribute name="app_name" />
    <attribute name="app_evaluationduedate" />
    <attribute name="app_evaluator" />
    <link-entity name="app_personnelreview"
                 from="app_personnelreviewid"
                 to="app_review"
                 link-type="inner"
                 alias="review">
      <attribute name="app_name" />
      <link-entity name="app_personnel"
                   from="app_personnelid"
                   to="app_personnel"
                   link-type="inner"
                   alias="reviewee">
        <attribute name="app_fullname" />
      </link-entity>
    </link-entity>
    <filter type="and">
      <condition attribute="app_personnelevaluationstatus"
                 operator="eq"
                 value="00000000-0000-0000-0000-000000000000" />
      <condition attribute="modifiedon" operator="last-x-hours" value="24" />
    </filter>
    <order attribute="app_evaluator" />
  </entity>
</fetch>

All three tables in one query. Results come back flat with aliased columns: review.app_name for the review cycle name and reviewee.app_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 is 1 + N + N API calls. With FetchXML, it is 1.

Dataverse supports up to 10 levels of link-entity nesting per query, and additional link-entities can be added at the same level. In practice, three is usually plenty. If you are nesting deeper than that, the data model probably needs attention before the query does.

Ordering for Recipient Grouping

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

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

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

With ordering, 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.

The savings are significant for any digest flow that fans out to multiple recipients. The exact action count depends on the recipient count and average rows per recipient, but the pattern reliably collapses nested-loop work into a single pass and reduces action consumption against the Power Platform request limits.

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.

A practical decision framework:

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 are building a quick prototype and do not 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

A reasonable workflow is to prototype with OData and switch to FetchXML the moment you hit a limitation. For notification flows, that moment usually comes on flow number one.

Performance Tips

A few practical guardrails that show up across most production FetchXML in Power Automate.

Select only the attributes you need. Avoid 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. The official guidance is in FetchXML performance optimizations.

Use exists link-types when you do not need columns from the joined table. If you are filtering by a related record’s status but do not need any of its fields in the output, link-type="exists" 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. List rows with FetchXML caps at the default page size without manual paging. If your notification flow could plausibly match more, 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 a flow inventory 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. Spec-Driven Notification Flows - 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.