Power Community

Power Community

Optimal ways to query large datasets (Dataverse) in Power Automate

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.

Shaanmathi_0-1686915095928.png 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‘.

Shaanmathi_8-1686851042529.png NoteThe maximum configurable threshold is 100,000.

Shaanmathi_0-1686904083534.pngShaanmathi_2-1686904153942.png

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

Shaanmathi_1-1686904539563.png

Shaanmathi_0-1686850580245.pngQuick 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.

Shaanmathi_2-1686851507388.png

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’))

Shaanmathi_0-1686902413172.png

Step 3: Add List rows action inside the Do until to retrieve the targeted rows by specifying the FetchXML. In the query, in 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)

Shaanmathi_0-1686902509538.png

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.

Shaanmathi_0-1686902826241.png

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

Shaanmathi_0-1686903408667.png

Shaanmathi_1-1686903449751.png

Shaanmathi_8-1686851042529.png 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. 

This post was originally published on this site

- Advertisement -spot_img

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisement - Advertisement

Latest News

Capture and visualize ideas without spatial constraints using Infinite Canvas in Visio for the web

Following our announcement earlier this year about mind maps in Visio for the web, we’re excited to announce a...

More Articles Like This

- Advertisement -spot_img