Fetching large number of data from Dataverse Connector in Power Automate

     Handling a large amount of data remains a challenge for many Power Automate enthusiasts. Here's an article detailing how to retrieve a large number of records from Dataverse using Power Automate. There are two methods for fetching data from Dataverse via Power Automate.

• Threshold Way(fetch records less than or equal to 100000)
• Paging Cookie way(fetch many records)

Threshold Way

To demonstrate this, I've set up a cloud instant flow. In the flow editor page, beneath the trigger, you need to add an action called "List Rows from Dataverse" (you can rename it if you prefer).

In the "List Rows" action, select the table name and add any necessary queries. For my purpose, I've chosen the Contacts table and selected only the fullname column from that table. Alternatively, Fetch XML can also be used for querying the same data.

The "List Rows" action of the Dataverse connector for Power Automate defaults to fetching 5000 records. However, by enabling Pagination in ellispse → Settings → Threshold (accepting values less than or equal to 100000), you can configure it to fetch records up to a count of 100000.

Add another compose action to view the records count. For that, write an expression to find the length of the outputs from the "List Contacts" action. Here's the expression provided:

length(outputs('List_Contacts')?['body/value'])

Save the flow and run it. Wait until the flow runs successfully. Open the run and expand the compose action; there you will see the record count. In my case, I have more than 100k records. However, since we set the threshold limit to 100,000 in the standard configuration, it will fetch records up to that limit.

Paging Cookie way

    The Dataverse connector for Power Automate has a threshold limit of 100K records. To fetch more than 100K records, you need to use the Paging Cookie method in Power Automate. This method can also be used for fetching records below the 100K limit. It retrieves only 5000 records per request, so you need to add requests in a loop and set the Paging Cookie for each iteration. The paging cookie, in XML format, contains the record IDs for the starting and ending records for each iteration, and it changes accordingly for every iteration.

We need to initialize several variables, including RecordsCount, PagingCookie, ClearPagingCookie, and EncodingJSON. Within the EncodingJSON object, we create a field named "t" to store the Paging Cookie.

Initiate a "Do Until" action with the condition to check whether the variable PagingCookie is not empty. Use the following expression to evaluate the variable:

empty(variables('PagingCookie'))

Then inside the "Do Until" loop, add a "List Rows" action for the contact entity with the FetchXML query. In the FetchXML query, include the "page" attribute, which defines the page number. Since the request returns only 5000 records in the first iteration, set the initial page number to 1, and it increments for each subsequent iteration. You can use the below expression to dynamically increment the page number by 1:

add(iterationIndexes('Do_until'),1)

To set the Paging Cookie, we need to check the iteration index of the "Do Until" loop and then assign the Paging Cookie value stored in the EncodingJSON variable. Use the following expression to set the Paging Cookie:

if(equals(iterationIndexes('Do_until'),0),'',concat('paging-cookie=''', substring(first(skip(split(string(xml(setProperty(variables('EncodingJSON'),'t',variables('ClearPagingCookie')))),'<'),1)),2),''''))

After the "List Rows" action, we need to update the Paging Cookie with the value obtained from the response of the "List Rows" action. Use the following expression to achieve this:

if(empty(outputs('List_Contacts')?['body']?['@Microsoft.Dynamics.CRM.fetchxmlpagingcookie']),'',decodeUriComponent(decodeUriComponent(outputs('List_Contacts')?['body']?['@Microsoft.Dynamics.CRM.fetchxmlpagingcookie'])))

To clear the existing Paging Cookie values, use the following expression:

if(empty(variables('PagingCookie')),'',replace(substring(variables('PagingCookie'),add(indexOf(variables('PagingCookie'),'pagingcookie="'),14)),'" istracking="False" />',''))

In this process, we need to retrieve the record count fetched during each iteration. We'll save this count in a variable and increment it by the current count in every iteration.

length(outputs('List_Contacts')?['body/value'])

Add a "Compose" action and assign the "RecordsCount" variable to it. This will display the final count of records. Save and publish the flow, then run it.

Once the Run is completed, scroll down and expand the "Compose" action to view the actual count of records. In your case, it should display a count potentially exceeding 100K.

Have a great day!