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)
You can solve the use of the extra step using expand query in the list record action. (More on how to below the image).
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.
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.
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:
If we want to expand all of the navigation properties above we can do that by adding them in the Expand Query like below.
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:
This will result in the JSON output below.
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!