Dataverse 'Expand Query' Feature
When working with Power Automate and Dataverse, querying data across related tables is a common requirement. One of the most powerful features to handle this is the ‘Expand query’ option in the Dataverse ‘List rows’ action. This allows you to compile records from a table while also retrieving data from a parent table.
In this blog post, I’ll walk through how to use ‘Expand query’ to retrieve and combine data from two related tables.
Scenario & Objective
We have two Dataverse tables.
County: Contains all the counties in the UK. It has a lookup column called County_Region that links to the Region table.
Region: Lists all the regions in the UK. Each region can have multiple counties.
The goal is to create a flow in Power Automate that pulls all counties from the County table, and for each county, include the name of the region it belongs to. This will be achieved using the ‘Expand query’ feature in the ‘List rows’ action.
Creating the Flow
Start by creating your desired trigger. For demonstration purposes, I will manually trigger my flow.
Add the ‘List rows’ action. Configure it to retrieve data from the County table. This action will fetch all the counties in Dataverse. If you want to filter the data you can do so using the ‘Filter rows’ Feature of the action.
As currently configured, there is no filtering of data and the action will retrieve all the data from the County table.
I also want the corresponding Region name for each county. To bring in related data from the Region table, click on ‘Show advanced options’ and use the ‘Expand query’ feature. This allows retrieval of the related parent records through the lookup column in the child table.
cr8d5_County_Region($select=cr8d5_name)
This OData query tells Power Automate to expand the County_Region lookup column, and select the Region Name column (represented by cr8d5_name). So, for each county, we’ll now also get the name of its related region. Note that the logical name of the column is used in the query.
Once the data is retrieved, the next step is to create an array that contains both the county and its region name. To do this I’m using the Select action to map the results and construct the array.
This will create an array where each entry contains the county name along with its corresponding region name.
Here is the full flow.
This is a sample of what the resulting output looks like:
[
{
"County Name": "Kent",
"Region Name": "South East"
},
{
"County Name": "Cornwall",
"Region Name": "South West"
}
]
Conclusion
By using the ‘Expand query’ feature in the Dataverse ‘List rows’ action, we can easily retrieve and work with data across related tables in Dataverse.
In our scenario, we created an array of counties, each paired with the name of its corresponding region, without needing multiple queries or complex logic. This method is a powerful way to streamline data retrieval when working with related tables in Dataverse.
Give this approach a go the next time you need to efficiently expand and query related Dataverse tables.
In most scenarios, you’re likely to want to filter the data too, so here is some additional info on how to Filter on a Dataverse lookup column.