Chris Munro

Use $$expand in Power Automate (Dataverse Cheat Sheet)

The problem

Most Power Automate flows that work with Dataverse look like this:

  • List rows (Contacts)
  • Loop through each record
  • Get related record (Account)
  • Use the data

It works—but it’s inefficient, slow, and harder to maintain.

Before vs After (Core idea)

❌ Before – Multiple calls + loop

  • 1 query → Contacts
  • N queries → Accounts (inside loop)
  • Total = 1 + N API calls

✅ After – Single query with $expand

  • 1 query → Contacts + Account data
  • Total = 1 API call

What $expand actually does

  • Pulls related table data inline
  • Eliminates extra actions
  • Works with:
    • Lookups (many-to-one)
    • Child tables (one-to-many)

Cheat Sheet

1. Basic $expand

Scenario: Contact → Account name

$expand=parentcustomerid_account($select=name)

Use in flow

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

2. Combine with $select (always do this)

$select=fullname,emailaddress1&
$expand=parentcustomerid_account($select=name)

Why

  • Smaller payload
  • Faster execution

3. Multiple fields from related table

$expand=parentcustomerid_account($select=name,accountnumber,telephone1)

4. Multiple expands

$expand=
parentcustomerid_account($select=name),
owninguser($select=fullname)

5. Filter using related columns

Scenario: Contacts where Account name = Contoso

$filter=parentcustomerid_account/name eq 'Contoso'

Rule

lookupSchemaName/field

6. Filter using GUID (better)

$filter=_parentcustomerid_value eq 00000000-0000-0000-0000-000000000000

Why

  • Faster
  • More reliable than text matching

7. Combine $filter + $expand

$select=fullname&
$expand=parentcustomerid_account($select=name)&
$filter=parentcustomerid_account/name eq 'Contoso'

8. One-to-many (child records)

Scenario: Account → Contacts

$expand=contact_customer_accounts($select=fullname,emailaddress1)

Important

  • Returns an array

9. Filter inside $expand

$expand=contact_customer_accounts(
    $select=fullname;
    $filter=statecode eq 0
)

10. Limit child records

$expand=contact_customer_accounts(
    $select=fullname;
    $top=5
)

Real Example (End-to-End)

Goal

Get:

  • Contact name
  • Email
  • Account name
  • Only active contacts
$select=fullname,emailaddress1&
$expand=parentcustomerid_account($select=name)&
$filter=statecode eq 0

Common mistakes (this is where flows go wrong)

❌ Using display names

Account/Name

❌ Not using $select

$expand=parentcustomerid_account

❌ Wrong polymorphic field

parentcustomerid

✔ Use:

parentcustomerid_account

How to find correct names

Use one of:

  • Dataverse → Tables → Relationships
  • FetchXML (export and inspect)
  • Browser network tab

Bottom line

  • $expand removes loops
  • $filter works across relationships
  • $select is mandatory for performance

admin