Using expand query with Microsoft Dataverse Connector | Power Automate

When using Power Automate to create automated flows one of the most commonly used connector is Microsoft Dataverse (formerly known as Common Data Service (current environment)). It contains a List rows action which has advanced options like Filter rows, Sort By, Expand Query. In this I have used Expand Query to get interlinked data between tables.

I had scenario where data had to be retrieved between a lot of interlinked tables (formerly entities). The tables are linked by lookups to another table. Here for example I have a Company table which stores a company’s details linked to Account table via lookup further linked to Contact table which is further linked to User table.

Linked Tables (Entities)

In the flow I wanted to retrieve the details like the company name, the account that company is handling, account’s primary contact and their email, owning user of the contact from all the interlinked tables . I used a ‘List rows’ action to retrieve the Company table and applied a basic OData query statecode eq 0 for filter rows to only retrieve active company records.

List Rows action

I have inserted a ‘Create HTML table’ action to see the output of previous List Rows step and using dynamic content added Company Name and Handled Account Name column.

Create HTML Table action

Here I got some alphanumeric characters instead of getting the actual name of the account.

Output of create HTML Table action

In this scenario instead of using multiple list rows and increasing the complexity of your flow you can use ‘Expand Query’ to get the interlinked tables data i.e. the handled account name (lookup to the account table). To retrieve the name column from account table syntax will be interlinked_table_schema_name($select=selected_field_schema_name)

Handled account which is lookup to Account Table

List Rows action with Expand Query to retrieve Handled Account name

Now you will get the Handled Accounts name (name column from account table name) in the dynamic content.

Retrieve Handled Account name with dynamic content

Handled Account name

Output of create HTML Table action

Now to further retrieve the primary contact of handled account (fullname, email column from contact table) and owner details of primary contact record (lastname column from user table) the expand query can be used as shown below. The expand query is nested here and hence, the notation $expand needs to be used.

Nested Expand Query syntax

But in the dynamic content for the nested queries the values are not coming.

Values not showing up in dynamic content

To get these values we need to manually input these in ‘Expression’ as these were nested queries. For this view the syntax from peek code.

Peek Code

Using the value from peek code as reference you can build the expressions as shown below.

Expressions for nested Expand Query

When you add these expressions they appear as item. But once you save and refresh the flow they will appear as other Dataverse dynamic content.

Adding the expressions as item

Create Table action after refresh

Test the flow and you will get the required output for your expressions.

Output of create HTML Table action

P.S. : FetchXML Builder in the XRM Toolbox can help you build your expand query expression. Using the Query Builder you can link entities and select attributes. Then go to View > Power Automate Parameters to view the parameters.

Build the query in FetchXML Builder and view parameters

Copy the expand query by clicking on it. You may not be able to see the whole query but it will be copied whole.

Copied Expand Query

You can see the results of the query by executing the query

Result View of Query

So this is how I used the expand query. I hope this helps you out 😊