Create a dictionary in a Power Automate flow to reduce number of Dataverse queries

In the realm of Power Automate, efficiency is paramount when orchestrating automated workflows, especially in the context of Dataverse integration. Dataverse, Microsoft’s robust data platform, serves as the backbone for managing and storing organizational data. As workflows become more intricate and data interactions multiply, the need to optimize operations becomes evident. One strategic approach to streamline and enhance performance is the judicious use of dictionaries within Power Automate flows.

Dictionaries, as dynamic data structures, prove to be invaluable assets in orchestrating efficient workflows. Their role in minimizing the number of Dataverse queries is particularly noteworthy, as these queries can often be resource-intensive and impact the overall performance of automated processes.

This article shows an example of creating a dictionary of country names and IDs which reduces the number of queries in the Power Automate flow, and makes the flow more efficient.

Tutorial: Creating a dictionary

Our dictionary will be stored in an object variable named vCountryKeyValues.

1. First, we initialize the variable. In this example Power Automate flow, we will store the ID and the name of a country record in the dictionary:

When the name of the country is processed in the flow (maybe as an example, we process a set of account record and update them with a reference to a country from a custom country table), we can have a look if the value already exists in the dictionary:

2. We use the string function string(variables(‘vCountryKeyValues’)) to check if the current value is already part of the dictionary object variable (in this example, the current country name is stored in a string variable named vCountryName).

If the condition is true, we can get the id of the current country name from the dictionary. Use the Data Operation – Compose action to access the value:

variables('vCountryKeyValues')?[variables('vCountryName')]

If the condition is false, which means there is no entry for the current country name in the dictionary yet, we will query the ID of the current country name from the dedicated table containing the country information. The ID is then stored in a string variable named vCountryId.

3. We will add the result of the query to the dictionary. It can be used for the next (account) record of which country information is processed in the flow run.

Use the Data Operation – Compose action.

We use addProperty function to add the new key-value pair to the dictionary object variable:

addProperty(variables('vCountryKeyValues'), variables('vCountryName'), variables('vCountryId'))

4. Now we add the new property to the dictionary object variable.

Variable: Set variable action is used here. For value, we use the output from the compose action in step 3.