Expand Query example in Power Automate

Using Expand Query in Power Automate with Dataverse | Dynamics 365

Dynamics 365, Power Apps, Power Automate, Power Platform / By

A simple example on how you can use the expand query in Power Automate to retrieve properties from related tables. Instead of listing cases and then have another action/step that gets the contact like the example below. (Example continues below the image)

Example of multi step for retrieving related table

You can solve the use of the extra step using expand query in the list record action. (More on how to below the image).

Image of the Expand Query field in the List rows action in Power Automate.

To find the properties you need for expanding the related tables you can do the following.

Step 1: Create a flow that uses the Dataverse List Rows action. In this example we trigger the flow manually and list cases in Dataverse. I have added row count to one to limit the amount of rows that gets listed.

Step 2: Next step I used the Compose action to output the result from the List Cases action. The output will be used to find the navigation properties that we need for expanding the related tables. Copy the output from the compose output.

Image of the output of a list rows action for retrieving cases in Dataverse.

Step 3: Open Visual Studio Code and search for navigation property. This property allows you to expand the related tables in the list rows action instead of using an own action step to retrieve the value for the related tables as previously shown.

Image of the navigation property from the JSON output in Visual Studio Code.

The properties in the example above is a reference related tables and can be expanded by adding them in the Expand query in the list rows action in the Power Automate flow.

The navigation properties from the image above:

  • subjectid
  • productid
  • owningbusinessunit
  • customerid_contact

If we want to expand all of the navigation properties above we can do that by adding them in the Expand Query like below.

Image of how to use Expand Query field in list rows action in Power Auomate

This will output a large json string with the properties from the case and all of the properties related to the related tables.

You can also specify the exact columns you want to retrieve from the related table by using OData query. For instance, to get the contactid, firstname, lastname and email of the contact on the case. you can use the following query:

customerid_contact($select=contactid,emailaddress1,firstname,lastname)
OData Query for expanding customer and selected properties.

This will result in the JSON output below.

Image of the JSON output from the OData Query in the Expand Query field.

You can then use expressions like below to retrieve properties from the expanded table.

Contact Id: first(body('List_Cases')?['value'])?['customerid_contact']['contactid']
First name: first(body('List_Cases')?['value'])?['customerid_contact']['firstname']
Last name: first(body('List_Cases')?['value'])?['customerid_contact']['lastname']
Email: first(body('List_Cases')?['value'])?['customerid_contact']['emailaddress1']

The reason that I’m using first is because I only want to output the first record in the List Cases action. This is to avoid that the flow automatically creates a for each loop when only listing one record like we do in this example.

The expressions above will output the following values in the compose step Get Properties From Expanded Table.

If you want to get the columns from the expanded table within an apply to each you can use expressions like below.

Contact Id: items('Apply_to_each')?['customerid_contact']?['contactid']
First name: items('Apply_to_each')?['customerid_contact']?['firstname']
Last name: items('Apply_to_each')?['customerid_contact']?['lastname']
Email: items('Apply_to_each')?['customerid_contact']?['emailaddress1']

This will result in this the following output:

Hope you found this useful!

For More Content See the Latest Posts