How to Use Expand Query for Dataverse Actions in Power Automate

Three List Rows actions. Two Apply to each loops. A Get a Row by ID inside each loop, just to fetch a single Account name from a Contact. The flow runs for 90 seconds. The run history looks like a spider’s web. And the API entitlement counter is climbing fast.

If you’ve built any meaningful number of Dataverse flows, you’ve lived this. The lookup column is right there on the row — the value sitting one relationship away — but Power Automate hands you back a GUID and an _value suffix, and you reach for another action to resolve it.

There’s a better way. It’s been in the action you’re already using.

In this guide, you’ll learn how to use Expand Query in Dataverse actions to pull related table data in a single action — covering the syntax Power Automate actually accepts, single and nested expansions, polymorphic lookups, the dynamic content trap nobody warns you about, and the governance trade-offs of using it across an enterprise estate.

Why One Action Beats Six

Every Dataverse action in Power Automate counts as a Power Platform API request. Each Get a row by ID you nest inside an Apply to each multiplies that count by however many rows you’re looping through — and burns through both your runtime and your per-user entitlement at the same time. A flow listing 500 orders and resolving the customer name on each one is 501 API calls. The same query with Expand Query is one.

That’s the practical case. The architectural case is cleaner still: Expand Query is a join. You’re telling Dataverse to assemble the related data server-side and return it in a single response, which is exactly what the underlying Web API was designed to do. The pattern of nested List Rows + Apply to each + Get a Row by ID is a low-code re-implementation of a JOIN, badly — slower, more fragile, and harder to read six months later.

The action you’re configuring is already calling the Dataverse Web API. Expand Query just lets you tell it to follow the relationship while it’s there.

What Expand Query Actually Is

Expand Query is the Power Automate field that surfaces the OData $expand system query option in the Dataverse connector. You’ll find it as an advanced option on two actions specifically: List rows and Get a row by ID. It does not exist on Add a new row, Update a row, or Delete a row — and that’s worth remembering when you go looking for it on the wrong card.

Under the hood, Dataverse exposes related tables through navigation properties. There are two kinds, and the distinction governs everything else about how Expand Query behaves:

• Single-valued navigation properties correspond to lookup columns — many-to-one relationships. The primarycontactid lookup on Account is the obvious example. Each Account row has at most one primary Contact.
• Collection-valued navigation properties correspond to one-to-many or many-to-many relationships. contact_customer_accounts on Account returns all Contacts associated with that Account.

Both can be expanded. They behave differently — particularly around paging and what you can do with nested expansions — and the Dataverse Web API docs are explicit about the limits: up to 15 $expand options in a single query, and each $expand creates a join that can affect performance. Treat that 15 as a ceiling, not a target. Most production flows use two or three.

The Two Syntaxes Power Automate Accepts

This is where the documentation reads cleanly and the reality is messier. Microsoft Learn shows two different syntaxes for the same field — and both work, but only one of them works at every depth.

The simplified Power Automate syntax drops the $select= prefix:

primarycontactid(contactid,fullname)

The full OData syntax keeps it:

primarycontactid($select=contactid,fullname)

For top-level expansions, both produce the same output. The List Rows documentation explicitly shows the simplified form as the entry-point example, then the full OData form everywhere else. The trap is this: the simplified form does not extend to nested expansions. The moment you need to expand a relationship inside a relationship, you must use the full OData syntax with $select= and $expand= separated by semicolons. Mix the two styles inside the same query and Dataverse will reject it.

The practical rule: pick the full OData form and use it consistently. The simplified form saves three characters and costs you future headaches.

Finding the Right Navigation Property Name

The single most common reason an Expand Query “looks correct but doesn’t work” is that the navigation property name isn’t what you think it is. Lookup columns and their navigation properties usually share a name for out-of-the-box tables — primarycontactid on Account is both the column and the navigation property — but for custom columns the navigation property is typically the schema name (PascalCase, with the publisher prefix), while the column’s logical name is lowercase.

The reliable ways to discover the right name, in order of how fast they are:

1. Run the query without Expand Query first. Use a Compose action on the List Rows output and look at the @odata.context and the raw JSON. The single-valued navigation properties appear with _value suffixes (_primarycontactid_value). Strip the leading underscore and _value suffix and you have the navigation property name for a simple lookup.
2. FetchXML Builder in XrmToolBox. Build the query you want as a FetchXML, then use View → Power Automate Parameters — the tool generates the exact Expand Query string you need. This is the fastest path for non-trivial joins.
3. The Level Up browser extension. Right-click a column in a model-driven app and pick Show logical names (and All Fields for polymorphic columns). Tells you what the platform actually calls the relationship.

⚠️ For custom lookups, the navigation property name is case-sensitive and does include your publisher prefix. cr6c4_Customer($select=cr6c4_customername,cr6c4_email) is correct; cr6c4_customer(...) will quietly fail to return data without raising an error you’d notice.

Expanding a Single Lookup — The Basic Case

Take a List Rows action against the Accounts table where you want each account’s primary contact name in the same response. The Expand Query field gets:

primarycontactid($select=fullname,emailaddress1)

That’s it. The action returns one record per account, each carrying a nested primarycontactid object with fullname and emailaddress1 inside it. In subsequent steps the values appear in dynamic content as Primary Contact Full Name and Primary Contact Email under the List Rows output — the designer auto-flattens single-valued navigation properties into the dynamic content pane.

The lookup payload in the JSON looks roughly like this:

{
  "name": "Contoso Ltd",
  "accountid": "7d51925c-cde2-e411-80db-00155d2a68cb",
  "primarycontactid": {
    "fullname": "Yvonne McKay",
    "emailaddress1": "yvonne@contoso.example"
  }
}

Notice the related row is nested inside the parent — not a separate array, not a separate request. That’s the difference Expand Query makes.

Expanding Multiple Lookups in One Query

Several lookups on the same row? Comma-separate them at the top level:

primarycontactid($select=fullname,emailaddress1),
owninguser($select=fullname),
parentaccountid($select=name)

Three lookups, one round trip. Each appears as its own nested object in the response, each carries its own selected columns. The order doesn’t matter; the spacing doesn’t matter; only the parenthesis balance does.

This is also the point at which you start counting your $expand options against the ceiling of 15 per query. Each one is a join. Stack too many and you’ll feel it in response times before you’ll hit the hard cap.

Nested Expand — Two Hops Through Related Tables

The case that justifies Expand Query existing at all: you need a value that’s two relationships away. From an Order, fetch the Customer (a Contact), and from that Contact, fetch the parent Account name.

The full OData syntax:

customerid($select=fullname,parentcustomerid;
  $expand=parentcustomerid_account($select=name))

Three things to notice. First, the inner $expand follows a semicolon, not a comma — semicolons separate options within one expansion; commas separate distinct expansions. Second, you must include the inner lookup column itself in the parent $select — parentcustomerid is listed in the Contact’s select clause, otherwise the inner expand has nothing to follow. Third, the inner _account suffix isn’t a typo; it’s the polymorphic resolution syntax, which the next section covers.

💡 The reason for the column-in-select rule is structural: $expand walks a navigation property that has to be present in the projection. Drop it from $select and you’ve told Dataverse “I don’t want this column” — and then asked it to follow a column you just told it not to return. Predictably, the platform agrees with you and returns nothing.

Polymorphic Lookups — Where the Syntax Breaks Down

Dataverse has a small set of lookups that can point to more than one target table: Customer (Account or Contact), Regarding (most activity-enabled tables), and Owner (User or Team). These are polymorphic, and they need different syntax.

You can’t expand parentcustomerid directly — Dataverse doesn’t know whether to walk it to Account or to Contact. Instead, you append the target table to the navigation property name:

parentcustomerid_account($select=name,accountnumber)

If a given Contact’s parentcustomerid happens to point to an Account, you get the data. If it points to a Contact, the expansion returns null. Most flows handle the second case by adding both expansions side by side:

parentcustomerid_account($select=name),
parentcustomerid_contact($select=fullname)

Then a downstream coalesce() or if(empty(...), ...) expression picks whichever one came back populated.

⚠️ The same rule applies to regardingobjectid on activity tables, where the target could be a Lead, Case, Opportunity, or custom table. There’s no shortcut — you append the logical name of each possible target. If your activities can be regarding ten different tables and you need a value from every one of them, that’s ten expansions. At that point the architecture deserves a rethink, not more $expand.

The Dynamic Content Trap

This is the gotcha that catches every developer once. Single-level Expand Query results appear in dynamic content. Nested Expand Query results do not.

Expand Query a primary contact on a List Rows over Accounts? Primary Contact Full Name shows up in the dynamic content pane downstream — exactly as you’d expect. Add a nested $expand inside that to fetch the contact’s parent account? The nested fields are present in the JSON output, but the designer won’t surface them as dynamic content.

You have two workarounds:

The Peek Code expression. Use the action’s ... menu, choose Peek code, and read the input JSON to find the property path. Inside an Apply to each over the parent records, the nested field is reached with:

items('Apply_to_each')?['customerid_contact']?['parentcustomerid_account']?['name']

The ?[] syntax is null-safe — if the property doesn’t exist on a given row (a Contact with no parent Account), you get null instead of an exception.

Parse JSON. Add a Parse JSON action with a sample of the List Rows output, generate a schema, and from then on the nested fields appear in dynamic content normally. This is the right call for non-trivial flows where you’re referencing the same nested fields in five different places — defining the contract once is worth the extra action.

The Peek Code path is faster for one-off references; Parse JSON is more maintainable across a large flow. There’s no third option that gives you dynamic content on nested expansions without one of these two.

A Worked Example — Vertex Manufacturing’s Order Reconciliation Flow

Take Vertex Manufacturing, a Pune-based industrial parts maker running a nightly reconciliation flow against ~3,200 orders in Dataverse. The original implementation looked like this:

• List Rows on Orders (1 call)
• Apply to each over orders (3,200 iterations) with three nested Get a row by ID calls per iteration — resolving Contact, Account, and User — for a total of 9,601 Dataverse API calls

Run time: 47 minutes on average. The flow occasionally timed out under peak load.

The rebuilt version uses a single List Rows with a nested Expand Query:

customerid($select=fullname,emailaddress1,parentcustomerid;
  $expand=parentcustomerid_account($select=name,vx_AccountManager;
    $expand=vx_AccountManager($select=fullname,vx_region)))

Total: 1 Dataverse API call, plus pagination as the row count crosses 5,000. Run time on the same 3,200 orders: under 90 seconds.

Two days of engineering work to refactor. 47 minutes to 90 seconds. The API entitlement footprint dropped by 99.99%, which mattered more than the runtime in this environment — the team had been buying capacity add-ons to cover the burn rate.

The lesson isn’t that Expand Query is magic; it’s that nested action loops over Dataverse data are an anti-pattern, and Expand Query is the platform-native fix.

A Few Practical Things to Know

A handful of constraints and behaviours worth keeping in mind before you go and rewrite every flow you own:

• The 5,000 row default still applies. Expand Query doesn’t bypass the row cap on the parent List Rows. Turn on pagination in the action’s Settings for larger result sets — the maximum configurable threshold is 100,000.
• Nested expand on collection-valued navigation properties can’t use $top or $orderby. Dataverse will throw error 0x80060888 if you try. Single-level expansions of collections support both.
• Nested expand on many-to-many (N:N) relationships isn’t supported at all. Same error code, different message. Switch to FetchXML for N:N traversals.
• $skip, $count, $search, and $levels aren’t supported inside $expand. Standard OData supports them; the Dataverse Web API does not.
• Parameter aliases work for $filter and $orderby but not inside $expand. Keep your expand options self-contained.
• The $filter= prefix isn’t allowed in Filter Rows or Expand Query fields — Power Automate adds it for you. Writing $filter=statecode eq 0 instead of statecode eq 0 produces a baffling error.
• Cached collection-valued expansions can return stale data. Lean on single-valued expansions where freshness matters.

How Expand Query Compares to the Alternatives

Expand Query vs nested List Rows / Get a Row. Expand Query wins on API entitlement and runtime almost every time. Use nested List Rows only when you need table-level filtering on the related rows that Expand Query can’t express cleanly — and even then, a $filter inside the expand often handles it.

Expand Query vs FetchXML. FetchXML is the more powerful tool — it supports aggregates and N:N traversals that OData can’t, and FetchXML Builder generates the syntax visually. Use FetchXML when the query needs operations Expand Query can’t reach. Use Expand Query for the 80% of cases where you’re following one or two lookups.

Expand Query vs Dataverse Search. Search is for finding rows by keyword across a full-text index. It’s not a relational join tool. Using Search for relationship traversal is the wrong shape.

Expand Query vs Power Fx. Power Fx in Power Apps can resolve lookups inline (ThisItem.PrimaryContact.FullName), but Power Fx isn’t available inside cloud flows. Expand Query is the cloud-flow equivalent of that one-liner.

What This Unlocks

The mental shift Expand Query asks of you is small but real: stop thinking of a flow as a sequence of CRUD steps where each related value gets its own action, and start thinking of it as a query that happens to be configured visually. The action is doing more than it looks like it’s doing — and treating it as a query plan, not a script, is what separates the flow that runs in 90 seconds from the flow that times out at 60 minutes.

Once you’re comfortable with the syntax, the patterns it enables compound. Reporting flows that previously needed ten actions become three. Approval flows surface the full context of a row in one fetch. Integration flows that push to external systems carry every relevant related field in a single payload, ready to map.

Ready to start? Microsoft’s Use lists of rows in flows reference is the authoritative spec for the field; the Join tables using OData page is where the deeper Web API rules live.

Got a tricky Expand Query scenario — a polymorphic lookup that’s misbehaving, a nested expand that won’t surface in dynamic content, a flow that should be one action but is currently six? Drop it in the comments. Always happy to dig in.