Understanding Filter rows and Expand Query in Dataverse (Power Automate)

Introduction

When working with Dataverse actions in Power Automate—especially List rows—you'll often deal with lookup columns.
At first glance, filtering or retrieving related data from lookup fields can feel confusing.

Two key concepts make this much easier:

• Filter rows

• Expand Query

This article explains what they are, when to use them, and how to use them correctly with real examples.

What Is Filter rows?

Filter rows is an option used in Power Automate (Dataverse – List rows action) to return only the records that match a specific condition, instead of fetching all rows from a table.

It uses OData filter syntax.

Using Filter rows with Normal Columns

Scenario

You want to retrieve Customers where the Customer Name is "ABC Corporation".

Filter rows:

cr6c4_customername eq 'ABC Corporation'

Note: cr6c4_customername is a Logical Name of Customer Name column.

Why this works

• cr6c4_customername → text column in the Customer table

• Text columns can be filtered directly using their values

Important Note ⚠️

• This works only when filtering directly on the Customer table

• If Customer is a lookup in another table (like Orders), you must use GUID-based filtering

What Is a Lookup Column in Dataverse?

A lookup column in Dataverse creates a relationship between two tables.

Example:

• Orders table

• Customers table
  The Order table has a lookup column called Customer that points to the Customers table.

Internally, Dataverse stores this as a GUID, not the customer name.

Why Lookup Columns Are Tricky in Power Automate

When you use List rows:

• You cannot filter directly using display values (like Customer Name)

• You must filter using GUIDs or OData relationship syntax

• To get related table fields, you need Expand Query

Using Filter rows with Lookup Columns

Scenario

You want to retrieve Orders where the Customer is a specific record.

Step 1: Identify the Lookup Schema Name

Example:

• Lookup column (Logical Name of lookup column in Orders table): cr6c4_customername

• Customers table primary key: GUID from Customer table

Step 2: Use Filter rows (GUID-based)

_cr6c4_customer_value eq '<Customer-GUID>'

✅ Why this works:

• cr6c4_customername → lookup column

• _cr6c4_customername_value → stores the related record GUID

• GUID → primary key of the Customers table and is retrieved from the List rows – Customers action.

Common Mistake ❌

cr6c4_customer eq 'ABC Corp'

❌ This will fail because Dataverse does not store text values in lookup columns.

What Is Expand Query?

Expand Query allows you to:

• Retrieve columns from the related table

• Avoid extra Get a row actions

• Improve performance

It works like a JOIN in SQL.

Expand Query Syntax (Basic):

cr6c4_Customer($select=cr6c4_customername,cr6c4_email)

Explanation

• cr6c4_Customer → lookup column (Schema name) in Orders table

• cr6c4_customername → logical name of Customer Name column

• cr6c4_email → logical name of Email column

This allows you to retrieve the Customer Name and Customer Email together with the Order data in a single action, eliminating the need for an additional Get a row step.

Conclusion

Understanding Expand Query and Lookup column filtering in Dataverse can:

• Reduce flow steps

• Improve performance

• Make your flows cleaner and faster

Once you master these two concepts, working with Dataverse in Power Automate becomes much more powerful and efficient 🚀