.png)
Are you tired of jumping through hoops to retrieve data from related tables in Power Automate? Say goodbye to cumbersome processes and hello to efficiency with the “expand query” feature. By leveraging the expand query in Power Automate, you can streamline the retrieval of properties from related tables without the need for additional steps. In this tutorial created by our RedCloud Digital Solutions Practice team, we’ll walk you through the process step by step, demonstrating how to set up the flow, identify navigation properties, utilize them, and parse JSON to get the desired output. Let's dive in!
Utilizing the expand query in Power Automate can streamline the retrieval of properties from related tables without the need for additional steps. Here's a brief example:
Step 1: Set Up the Flow
Begin by creating a flow that uses the Dataverse List Rows action. In this demonstration, the flow is manually triggered, listing “Accounts” from Dataverse. To limit the output, a row count is set to one.
Step 2. Identify Navigation Properties
Subsequently, use the Compose action to output the result from the List “Account” action.
This output serves to identify the navigation properties necessary for expanding related tables. Copy this output.
Step 3. Utilize Navigation Properties
In Visual Studio Code, search for navigation properties. These properties enable the expansion of related tables directly within the list rows action, eliminating the need for separate steps to retrieve related table values.
To specify specific columns from the related table, OData queries can be employed. For instance, to retrieve Country details (Region, Location, and Currency) associated with a case, the following query can be used:
Step 4: Parse JSON
OData for expanding Country and selected properties will result in the JSON output shown below.
By generating a sample from the JSON above, it will result as the following output in your flow.
