Squeezing Performance out of Microsoft Power Automate #1

David Schiefer -

I've been working with Microsoft Power Automate (formally Flow) since late 2018, when I was looking for a solution that would help me and my team keep track of IT assets. What started as a relatively simple SharePoint instance quickly developed into a setup that reduced manual work, brought different departments' processes together with ease. Over time, Power Automate helped improve this solution with a variety of features, such as depreciation and warranty tracking, handover form generation and more.

As it is the case for most Power Automate users, I ran into my fair share of issues / complications when creating my flows, so I plan to run a short series on common tips and tricks to keep in mind when you are creating your own, with a focus on efficiency.

Mmmmhh, zesty!
Wait, isn't Power Automate fast already?

As ye old English saying goes, there's "more than one way to skin a Flow". Your goal may be achievable using different bricks that Microsoft puts at your disposal, but that may not be the most efficient way to do so. It's also worth keeping in mind that Power Automate doesn't give you warnings about any inefficiencies you may introduce into your flow. I'm going to skip for now the more obvious candidates such as forgetting to enable concurrency, extracting only the data you need, expensive string operations etc. and will today focus on so-called expand queries.

Expand Queries

Expand queries was a feature that quite honestly I overlooked for quite some time. In a nutshell, an expand query allows you to query data that is connected to one another via foreign keys in one go, i.e. creating a smart filter for your data.

Example

Let's imagine a hypothetical Microsoft Dynamics 365 instance with three areas:

  1. Accounts
  2. Territories
  3. Users

Accounts links to Territories via the key territoryid, which is a GUID. Territories links to Users via the key managerid, which is also of the GUID type. We're looking to filter certain accounts that belong to specific territories, as well as specific area managers. Without knowing about expand query, we could now employ one of two approaches:

An example of how not to do things.

Approach 1: Add three List Records bricks to our Flow, setting the Entity Name field to Accounts, Territories and Users respectively. Stick the Accounts brick into an Apply to Each loop and then do the same for Territories and Users while comparing the GUID's and other fields of relevance. This is very inefficient and absolutely not recommended.

Approach 2: Add three List Records bricks to our Flow, setting the Entity Name field to Accounts, Territories and Users respectively. Use an ODATA Filter Query to extract accounts that are of interest and iterate through the list. Dynamically create ODATA Filter Queries that contain the iterated object's GUID in order to return the Territories and Users you are looking for. A bit more efficient than the first approach, but rather messy and still slow.

With an expand query however, we can approach this problem in another way:

Approach 3: Define an ODATA Filter Query to extract the accounts you are interested in (if applicable) and enter the following expand query into a single List Records brick:

territoryid($select=name,_managerid;$expand=managerid($select=firstname))

You may then iterate through the results, which will contain accounts that match our filter, in this case, a specific area manager, as well as the expanded fields. According to my measurements, this instant drill-down approach outpaced all other methods of data filtering by several hundred percent and made the flow significantly less complicated.

Buzz buzz, here come the bugs!

I don't want to sound pessimistic, but Power Automate can still be buggy as hell. I recently ran into the following random error when using expand queries:

Could not find a property named 'managerid' on type 'Microsoft.Dynamics.CRM.territory'.

Checking the Microsoft documentation on this topic, managerid is definitely a valid key. Running the same Flow yielded various results - either a timeout, the error above, or a successful execution of the Flow.

Still trying to figure this one out - I will update this post when I know more.

Update 28/12/2021

This issue appears to be caused by a bug in the List Records action. As this action is deprecated, don't expect a fix. There is however a workaround via the Dynamics 365 API - checkout this follow-up article.