Squeezing Performance out of Microsoft Power Automate #2

In my previous post I covered the concept of so called Expand Queries which allow you to quickly query linkages between related data sets, using a sample Dynamics 365 instance as an example. Towards the end of that post I ran into an issue - the step containing the expand query triggered several retries before it ultimately failed. I was determined to dig further and find out why.

Background

Each action that is dragged into an automation represents at its core an individual API call within the Microsoft environment, which exist at varying degrees of complexity. As the flow is executed, each action's underlying API call returns a JSON response that indicates whether the operation was successful or not as well as any calculated return data, where applicable. Power Automate in turn parses each response and informs you of the outcome via its user interface.

Looking back to the action we used in the previous blog post, List Records, we would have the following options at our disposal:

1. Select the entity we want to obtain data from in Dynamics 365.
2. Define a filter query in ODATA format to return only specific results.
3. Define an order query in ODATA format to determine the order of the returned results.
4. Define a top count, i.e. the maximum amount of results to return.
5. Define an expand query, which, as discussed in the previous blog post, allows us to also return additional data related to the chosen entity that we are interested in.

When this action is executed by Power Automate, it constructs a query using the Dynamics 365 Web API and the information we have entered for the five points above and then executes it.

Digging in

Since my expand query wasn't working as it should, I decided to explore the API a little further and to try and emulate the API call that the List Records action was creating and running, minus URL encoding for better readability:

https://contoso.dynamics.com/api/data/v9.0/accounts?$select=name,address1_longitude,address1_latitude,address1_city,address1_line1,address1_stateorprovince&$filter=territoryid ne null and statecode eq 0 and statuscode eq 1&$expand=territoryid

A quick breakdown of the query:

1. $select - This parameter allows us to limit the returned results to specific fields only. This will have a positive impact towards performance, since we are only querying the record properties that we are really interested in. A list of valid fields for the Accounts entity can be found here, though your org's Dynamics 365 entity may have additional custom fields available as well.
2. $filter - This parameter allows us to exclude specific records from the results that are returned using an ODATA filter.
3. $expand - This parameter allows us to specify one or more (up to 11) related keys to be expanded.

Executing the query in my web browser yielded a JSON response with status code 200, containing the data I was interested in - we're in business!

Making the API call and working with the data

In order for Power Automate to make the API call we have constructed, we will need to take advantage of the HTTP actions it offers. Two exist, but only one is suitable for this scenario:

1. HTTP - This action supports both API calls as well as basic authentication. However, it isn't suitable for Dynamics 365 because it requires all communications to be authenticated via SAML (Office 365 Single Sign-on).
2. Invoke a HTTP request - This action authenticates all requests it makes via the user that is associated within the Flow with this particular action and will do just fine.

Invoke a HTTP request requires two parameters, method, for which we will select GET and Url of the Request, which will contain our API call. To work with the results, we now simply drag a Parse JSON action into our flow and that's it!

Conclusions

In this post I have provided a brief insight into how Power Automate actions work in the background and how you can leverage this to your advantage, whether that is to circumvent certain bugs or to optimize the overall performance of your automation. Keep in mind that API's change from time to time, so it's important to be on top of any updates from Microsoft's side or your automation might suddenly stop working one day.