This article is part of the series of useful tips and tricks that can be used in Power Automate flows.
In part 1 of this series, I will explain how to reduce the number of queries that are executed in a flow by creating a local dictionary to act as a cache.
Creating a dictionary variable in a flow
In some scenarios, we need to get a list of rows from Dataverse or another data source and then for each item in the list another query needs to be issued to get some other data required to process the item.
For example, consider a scenario where a list of contacts is read from an excel file and then for each contact, the parent account is queried using the account name column in the excel file. If the excel file has 500 rows and the flow queries account table for each row, then 500 requests will be sent to the Dataverse which is not an optimal solution.
What if we could use a local variable inside the flow to cache the results and then use the cached results to reduce the number of requests that are sent to the Dataverse?
Fortunately, it is possible to create a dictionary inside a flow and use it as a local cache to store the results.
The following example shows the process that can be used to create a local dictionary inside a flow:
- First, we need to initialise a new variable with the type of Object. The following screenshot shows how an object variable can be initialised with an empty object (an object without any properties):
As can be seen in the above image, the “{}” was used to assign an empty object to the Cache variable.
- Next, we need to create a dictionary that maps the account name to the account record. If the number of rows that needs to be cached is less than a few hundred (e.g. 1000 records), all of the records that need to be cached can be read in one request and then processed as shown in the following screenshot:
The trick here is to use the compose action to add a new property to the cache object. The following expression has been used in the compose action to add a new property to the cache object:
setProperty(variables('Cache'),items('Apply_to_each_account')?['name'],items('Apply_to_each_account'))The output of the compose action then has been used to update the local variable as shown in the following image:
- As the “Apply to each action” action may process the rows in parallel, we need to configure the concurrency on the loop to process one record at a time as shown below:
- Next, the data that needs to be processed will be fetched from the data source. In this case, the data will be read from an excel file.
- Then, the output of the previous action will be processed as shown below:
As shown in the above image, another compose action has been used to map the account name to the cached account object. The expression that was used to map the row is as follows:
variables('Cache')?[items('Apply_to_each_excel_row')?['Account Name']]- Finally, the account id from the object that was retrieved from the cache has been used to create a new contact record in the system as shown below:
The expression that was used to get the account id from the output of the compose action is as follows:
outputs('Get_account_row_by_account_name_from_the_cache')?['accountid']The following image shows the screenshot of the flow:
In summary, it is important to reduce the number of API calls in a flow if possible because not only it improves the flow performance, but it also reduces the overhead on the data source. If the datasource that is used in the flow is Dataverse, reducing the API calls also prevents reaching the API limit that is in place in Power Platform.