Page large result sets (Dataverse) in Power Automate

[Shaanmathi]

Microsoft Dataverse limits the maximum number of rows that can be retrieved in a single query to 5000. But often while working with large datasets, there occurs a need to fetch more than 5000 rows and this blog will discuss how it can be achieved in Power Automate.

 List rows action in Power Automate is used to retrieve multiple rows at once from Dataverse with a structured query - 'Filter rows' or 'Fetch Xml Query'. Filter rows is an OData-style filter expression used to narrow down the set of rows that Dataverse returns whereas Fetch Xml Query is a Dataverse-style FetchXML query which allows more flexibility in building custom queries especially while working with a table that has multiple related tables. 

Filter rows:

While using Filter rows to retrieve more than 5000 rows, 'Pagination' feature must be enabled in the Settings and the maximum number of requested rows must be entered in 'Threshold'.

 Note: The maximum configurable threshold is 100,000.

Enabling this feature helps to retrieve rows up to the specified threshold in a single query as shown below,

Quick tip: While paging large result sets, it is best to process the workload considering the content throughput limits and message size limits that Power Automate applies to ensure general service guarantees.

Fetch Xml Query:

Unfortunately, the Pagination feature does not work when Fetch Xml Query is used to retrieve more than the default limit of 5000 rows. In this case, 'page' attribute can be used in the FetchXML to page through and retrieve a large dataset. The below is a simple approach to achieve this with minimal actions.

Step 1: Initialize two variables - MoreRecords and Total Rows Count (Optional).

MoreRecords - A string variable to store the value of "@Microsoft.Dynamics.CRM.morerecords"

(While querying a set of rows, if there are more rows available in the upcoming pages, the value of "@Microsoft.Dynamics.CRM.morerecords" in the body of the result set will be true. If there are no further records available, "@Microsoft.Dynamics.CRM.morerecords" will not be available in the result set.).

Total Rows Count (Optional) - An integer variable to store the count of the total rows retrieved.

Step 2: Add a Do until control to retrieve the rows until MoreRecords variable is empty i.e., until there are no more rows available to be retrieved.

Expression: empty(variables('MoreRecords'))

Step 3: Add List rows action inside the Do until to retrieve the targeted rows by specifying the FetchXML. In the query, in <fetch> element add 'page' attribute to mention the number of the page of the result set to be retrieved. The value of 'page' must be an incrementing value in accordance with the iteration of Do until in order to retrieve the upcoming pages of the result set.

Expression: add(iterationIndexes('Do_until'),1)

Quick tip: The paging cookie is a performance feature that makes paging in the application faster for very large datasets. When querying a set of rows, the result will contain a value for the paging cookie ("@Microsoft.Dynamics.CRM.fetchxmlpagingcookie"). For better performance, this value can be passed to retrieve the next set of rows.

Step 4: Inside the Do until after the List rows action, set MoreRecords with the value of "@Microsoft.Dynamics.CRM.morerecords" from the body of List rows result.

Expression: body('Get_Accounts')?['body']?['@Microsoft.Dynamics.CRM.morerecords']

Follow it by incrementing the Total Rows Count with the count of rows retrieved.

Expression: length(outputs('Get_Accounts')?['body/value'])

Finally compose Total Rows Count outside the Do until to see the aggregated count of total rows retrieved.

Below is the result of running this sample flow to page results of Fetch Xml Query,

 Note: Ensure that the Pagination feature is disabled while using Fetch Xml Query to retrieve multiple pages of large result sets. Else, only up to 5000 rows (default limit) will be fetched and no information regarding the next page of rows will be available in the retrieved result set.