Power Automate: Efficiently Retrieve Dataverse Lookup Values Using Expand Query

By

Introduction

When building Power Automate flows with Microsoft Dataverse, retrieving lookup column data is a very common requirement.
However, many flows are designed in a way that retrieves only lookup GUIDs, forcing additional Dataverse calls to fetch related data. This approach increases complexity, execution time, and API usage.

In this article, we’ll see how to efficiently retrieve lookup values in a single call using the Expand Query option in the Dataverse List rows action.


The Challenge with Lookup Columns

Consider a Dataverse table cs_ticket that has a lookup column cs_CustomerContact pointing to a Contact table. When you retrieve rows using List rows without an expand query, the lookup column returns only the GUID.

Sample output

"_cs_customercontact_value": "7c8ffb96-00a7-47c0-b587-a6c652d66b34"

Problems with this approach

  • You cannot access contact details like email or contact number

  • You need an additional “Get a row by ID” action

  • Flow becomes slower

  • Increased Dataverse API calls


The Solution: Use Expand Query

The Expand Query option allows you to retrieve related lookup data inline, avoiding extra Dataverse calls. With $expand, related entity fields are returned as part of the same response.

Step 1: Add the List rows action and select your Dataverse table.



Step 2: Configure Select columns, Expand Query



Expand Query: cs_CustomerContact($select=cs_contactname,cs_contactemail)

Tip: $expand always uses the lookup schema name, while $select inside expand uses logical column names from the related table.


Output Comparison

❌ Without Expand Query

"_cs_customercontact_value@OData.Community.Display.V1.FormattedValue": "Ben",
"_cs_customercontact_value@Microsoft.Dynamics.CRM.associatednavigationproperty": "cs_CustomerContact",
"_cs_customercontact_value@Microsoft.Dynamics.CRM.lookuplogicalname": "cs_contact",
"_cs_customercontact_value": "7c8ffb96-00a7-47c0-b587-a6c652d66b34",

You must make another Dataverse call to get contact details (e.g. email address).

✅ With Expand Query

"cs_CustomerContact": {
                    "cs_contactid": "d1a22df0-1316-4cab-bb20-4dc67fabdaf0",
                    "cs_contactname": "Cal",
                    "cs_contactemail": "cal@contoso.com"
                }

All required data is retrieved in a single call.


Why This Approach Is Better

🚀 Performance

  • Single Dataverse call

  • Faster flow execution

💰 Lower API Consumption

  • Fewer Dataverse read operations

  • Scales better for large datasets

🧹 Cleaner Flow Design

  • No extra “Get row by ID” actions

  • Easier to debug and maintain

🧠 Best Practice

  • Recommended for production-grade flows

  • Aligns with Dataverse OData standards


Conclusion

By leveraging the Expand Query option in Power Automate’s Dataverse List rows action, you can retrieve lookup column data efficiently and avoid unnecessary additional calls. This approach results in faster flows, reduced API usage, and cleaner automation design.

If your flow relies on lookup data, Expand Query should be your default approach.

Comments

Post a Comment

Popular posts from this blog

Validate Email Format in Power Automate Using Custom Connector and Regex in C#

By
Image
Validating email formats in workflows is important to pre-check the data before using it in business logic and to avoid errors. While Power Automate has many actions, it doesn’t include advanced email validation by default. In this blog, you’ll learn how to create a  Custom Connector  to validate email addresses using  C# code and Regex . High-Level Solution: Create a Custom Connector  in Power Automate that uses C# and Regex for email format validation. Use the Custom Connector  in Power Automate flows to dynamically validate email addresses. Steps to Implement: Step 1: Create the Custom Connector 1.1 Create Blank Custom connector Log in to  Power Automate . From the left menu, select  Custom Connectors . If not visible, go to  More  >  Discover All  > Locate  Custom Connectors  under the  Data  section. Click on  New Custom Connector , then select  Create from Blank . 1.2 General Configuration E...
Read more

Integrating ServiceNow with Power Automate via REST API

By
Image
In this article, we’ll explore the integration process of ServiceNow with Power Automate using the REST API and OAuth 2.0 Resource Owner Password Credentials, accompanied by an illustrative example — retrieving Service Request details from ServiceNow using Power Automate. Power Automate includes a built-in ServiceNow connector. In this blog post, we’ll explore an alternative method for establishing a connection with ServiceNow. Prerequisites: ServiceNow Instance:  Access to a ServiceNow instance with the necessary permissions to create and manage API integrations. Power Automate Account:  A Microsoft Power Automate account to create and manage automated workflows. Step 1: Register OAuth Application in ServiceNow Start by logging into your ServiceNow instance. Search for “ Application Registry ” and select the corresponding option. 3. Click on “ New ” 4. Select “ Create an OAuth API endpoint for external clients .” 5. Name your application (e.g., PowerAutomateApp) and leave the...
Read more

How to Check Null Values in Power Automate Filter Query

By
Image
Working with data in Power Automate often involves the need to filter records based on null values. In this blog post, we’ll explore the correct approach to check for null values in the Filter Query and address common misconceptions. By understanding the nuances, you’ll gain the ability to handle null values effectively and achieve accurate results. What Doesn’t Work? There are a few common mistakes to avoid when checking for null values in the Filter Query: Field eq ‘ ‘ (Space): Using a space (‘ ‘) within the quotes won’t yield the desired results. It treats the space as a non-null character and won’t match records that have null values in the field. Field eq {null} (Null Expression): Writing ‘null’ within the quotes won’t work either. The expression will be treated as a string comparison and not evaluate to null values. It won’t filter the items as expected. Field eq ‘null’ (String Comparison): Using the string ‘null’ within the quotes won’t correctly identify null values either. It ...
Read more