How to loop through more than 100 000 records in a Dataverse table

When using the List Row’s action in Power Automates Dataverse connector, the default item limit is 5000 items.

If your table contains more than 5,000 records, you can enable Pagination in settings on List rows action. This can give you up to 100,000 records.

If the table has more than 100,000 records, you’ll need to use slightly different techniques to return all records. This involves turning off Pagination in List Row’s action and using Skip Token instead. When Pagination is not on, the response from the action will contain a @odata.nextLink parameter that can be used to query for the next set of records. When Pagination is off, 5,000 records are default per request. By parsing @odata. NextLink and get the skip token you can use this in the next query with List rows to get the next 5000 records. That’s how you can loop until @odata.nextLink is empty, then you should get all records.

Power Automate flow sample to retrieve all records

First a List Rows, no Pagination on. This will return the first 5,000 records and return a @odata. NextLink parameter with a ship token we can use to get the next 5000 records.

After first querying with List Rows, we will get a @odata.nextLink (if the table contains more than 5000 records). We need to parse this to get the Skip token. Skip Token should be used in the next query with List Rows to get that in the next set of records. Create a String variable and use the following expression to obtain Skip Token:

if
(
    empty
    (
        outputs('List_rows_2')?['body/@odata.nextLink']
    ),
    '',
    decodeUriComponent
    (
        last
        (
            split
            (
                uriQuery(outputs('List_rows_2')?['body/@odata.nextLink']),
                'skiptoken='
            )
        )
    )
)

“List_rows_2” is the name of your List Row action

Then set up a Do until loop where we loop until there is no return of a @odata.nextLink, which means we have got all records.

We need the new Skip Token from the List Rows query inside the Do until loop. Use expression, refer to List rows in the loop and get your hands on the next ship token. This Skip Token is used in List Rows in the do Unit loop.

In addition to this, we have to change the timeout value of the Do until loop. default is that it has a timeout of 1 hour. This is far too small when we talk about over 100,000 records. Click on Change limits and change the timeout to e.g. PT30H this is a timeout of 30 hours for the Do until loop. Timeout value uses ISO 8601 standard

Power Autmate should now loop through all records in a dataverse table. My test of about 200 000 rows in a table took close to 30 hours to run.

Power Automate Flow Overview

References

Retrieve more than 100,000 Dataverse Rows with List Records Action in Cloud Flow Using Skip Token (linnzawwin.blogspot.com)

Use lists of rows in flows – Power Automate | Microsoft Docs