Using expand query in Power Automate Flow.

Hello Guys,

In our previous blog we have seen how to update lookup field for entity using MS Flow. Today we will see how to use expand query in MS Flow.

Do follow us to know more about Dynamics and Power Platform. Also if you like our blog then please comment and share this blog with your friends.

Consider a scenario where we have contact, account, country and region table.

Contact entity is having lookup of account and country. Also country entity is having lookup of region.

Now we want to retrieve contact record along with account, country and region. So we want while retrieving contact it should also retrieve fields of account, country and region. let's check how it works!

We have created one manual flow which will retrieve contact based on GUID as shown below:

Now when we run this flow, it will retrieve all fields of contact as shown below:

As you can see our lookup fields are also there. Now if you notice it has not retrieved region as region field is not available on contact. It is available on country table. Also for lookup it's retrieving Guid, logical name and value. Now we want website field which is available on account table should also be retrieved. We can achieve this using expand query which is similar to inner join in SQL. Let's see how to do this. Open the flow and specify the expand query as shown below:

Here in above image parentcustomerid_account, blg_country, blg_regionid are lookup fields for contact and country table. These fields are also known as navigation property.

Now we will see how to read these values from the response. we will initialize three variables to store the values. 

We have used below formulas to achieve those values.

outputs('Contact')?['body/parentcustomerid_account/websiteurl']

outputs('Contact')?['body/blg_country/blg_name']

outputs('Contact')?['body/blg_country/blg_regionid/blg_name']

Now we will run the flow and check the values.

As you can see, all the values are coming.

Hope it helps...