How To Fetch More Than 100k Items From Dataverse Table

Introduction

In Power Automate, the default threshold limit for the "List rows" action is 5000, which is used to fetch items from the Dataverse table. However, we can enable pagination to increase the limit to 100,000 using this action. This article will teach us how to fetch more than 100k items from the Dataverse table.

Recently, while working on one of our case studies, we had a requirement to fetch more than 100k items from a Dataverse table and perform certain operations. In this demo, we will see how to query the Dataverse table in batches of 5k records to fetch large amounts of data.

Prerequisites

• Dataverse table with more than 100k records
• Power Automate

Below are the steps.

Step 1

Create a Power Automate flow and initialize three variables for Page Number, Paging Cookie, and Item count.

 

Step 2

Insert a 'Do until' action and configure the condition as shown below to query the Dataverse table and retrieve records in batches until the 'Paging Cookie' variable is not empty.

@and(not(equals(variables('page number'),1)),equals(variables('Paging Cookie'),''))

Step 3

Add 'List rows' and 'Increment variable' actions to increment page number count.

Step 4

Set the 'Paging Cookie' variable with the following expression to extract the Paging Cookie from the previous query in the loop.

if
(
    empty(outputs('List_rows')?['body']?['@Microsoft.Dynamics.CRM.fetchxmlpagingcookie']),
    '',
    replace
    (
        replace
        (
            replace
            (
                decodeUriComponent
                (
                    decodeUriComponent
                    (
                        first(split(last(split(outputs('List_rows')?['body']?['@Microsoft.Dynamics.CRM.fetchxmlpagingcookie'], 'pagingcookie="')), '" '))
                    )
                ),
                '<', '&lt;'
            ),
            '>', '&gt;'
        ),
        '"','&quot;'
    )
)

Step 5

Set the item count variable to get the total count using add function.

add(variables('varItemCount'),length(outputs('List_rows')?['body/value']))

Output 

In this demo, flow queried the Dataverse table 21 times to fetch 104,246 records in batches of 5,000.

Conclusion

This article has provided a step-by-step guide on how to fetch more than 100k items from a Dataverse table by querying the table in batches of 5k records using a paging cookie in Power Automate. Following these steps, we can retrieve and perform operations on a large amount of data from the Dataverse table.