How to Turbocharge Your Power Automate Queries! 🚀

Brian Knight from Pragmatic Works walks users through how to use the "Expand Query" feature in Power Automate to enhance the performance of their workflows. The video covers practical scenarios where pulling data from related tables in Microsoft Dataverse can significantly reduce the complexity and number of steps in a flow.

Introduction to Expand Query

Have you ever encountered the “Expand Query” option while working in Power Automate and wondered what it does? Brian explains that this feature allows users to retrieve data from linked tables in Dataverse in a single operation. This can dramatically improve the performance of flows by eliminating the need for additional steps like "Get Row by ID" for each related piece of data.

Example Scenario

Brian demonstrates how he uses Expand Query in a flow designed to loop over a list of contacts. In the scenario, he wants to send an email to each contact but needs to pull additional information from related tables—specifically, the contact owner's name and email address. Without Expand Query, he would need multiple actions to retrieve this data. Expand Query simplifies the process by allowing all of the information to be pulled in one step.

Using XRM Toolbox

Instead of writing complex FetchXML queries manually, Brian utilizes the XRM Toolbox, a free tool that simplifies query building. He walks users through connecting to the Contact entity in Dataverse and linking it to the "Owning User" table to retrieve the owner’s details. Once the link is established, he selects the fields he needs, such as the owner's full name and primary email address.

Converting to Power Automate

Once the query is built, Brian demonstrates how to convert it into parameters that Power Automate can understand. By using the "Convert to Power Automate Parameters" option in the XRM Toolbox, he easily integrates the Expand Query into a Power Automate flow. This not only reduces the steps required to pull data from multiple tables but also significantly boosts the performance of the flow.

Improving Performance

Brian emphasizes that while the Expand Query feature requires some upfront setup, it pays off in performance improvements. By retrieving multiple fields from multiple tables in a single step, users can avoid the performance bottlenecks that come with repetitive Get Row actions inside loops.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power Automate and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.