How to retrieve more than 5000 records with Power Automate

The list rows action from the Dataverse connector in Microsoft Power Automate has a limit 5,000 rows by default. We will see below the different ways of retrieving more :

• Use Pagination
• Use SkipToken

Everytime I have to retrieve more than 5000 records from Dataverse in Power Automate, I stuck for few minutes.

Here what cross my mind :

• 🤨 Should I launch manually few times my flow to cover the whole bunch of items ? (Ugly I admit it, but I really think about it 😋)
• Do I use the Pagination ?
• Do I use the Skip Token ?

Now let's imagine I decide to be lazy and not click 20 times to cover the whole 100000 records present in Dataverse

Here is my decision path, depending on the number of records to retrieve :

• Between 1 to 5000 -> simplest one
• Between 5000 to 100000 -> Pagination
• More than 100000 -> Skip Token

List Rows : 5000 rows limit

A quick test on the Contact entity without any filters shows it

Well in fact not really, because if I select all the columns, here is what happens :

It fails \o/

Why the buffer size error in List Rows ?

Because you exceeded the 100 MB limit

You can find how to solve this issue with Avoid buffer size error for List Rows using Power Automate Flows

Ok let's select only 1 column and go with the Pagination way of doing

How to use Pagination to retrieve more than 5000 records in Power Automate

• Open the ... of the List Rows action
• Choose Settings
• Toggle on Pagination
• Input 10000 as Threshold

You can see now that I retrieved more than 5000 records but I hit the 10000 threshold limit I defined previously. It just mean I have more than 10000 records

Let's do it again and change the Threshold to 100000 records

Fantastic, but what if I have to retrieve more than 100000 records?

WAIIIITTT, before going further, there is a limitation you should know

⛔ Problem : Pagination does not work with Fetch XML

Pagination does not work when you are using the Fetch XML query to filter

🟢 Solution : Convert your Fetch XML to OData Filter

How to use SkipToken to retrieve more than 100000 records in Power Automate

Wait, can I really have more than 100000 records in ONE entity ?

Yes you can, take a look at your activitypointer table for instance

We hit the threshold, meaning there is more than 100000 rows matching our request.

If the table has more than 100000 records, you have to use a different technique to return all records.

The steps are :

• Turn Off Pagination in List Row’s action
• Parse the odata.nextLink parameter
• Extract the Skip Token
• Loop until the odata.nextLink is empty

What is Skip Token in odata.nextLink parameter

When Pagination is off, the response from the action contains :

• a @odata.nextLink parameter
• and a batch of 5000 records :

@odata.nextLink : https://xxx.crm4.dynamics.com/api/data/v9.1/activitypointers?$select=subject&$skiptoken=%3Ccookie%20pagenumber=%222%22%20pagingcookie=blablabla

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.

How to retrieve more than 100000 records using Skip Token

Here is the structure of the flow

Prerequisites

• Deactivate pagination (otherwise odata.nextLink will be empty)

List Rows with skipToken Initial Query

We get the odata.nextLink with an initial query.

Initialize SkipToken variable

You initialize the SkipToken variable by extracting its value from the odata.nextLink retrieved just before

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

Do until SkipToken is empty

The Count parameter corresponds to the max number of iteration of your loop.

• Default value is 60, meaning you can retrieve 60*5000 records = 300000 records
• By putting 5000, you can retrieve 25000000 records (yes, 25 millions records)

The Timeout parameter is the time duration of how much your loop should run

• Format is ISO 8601
• PT1H is one-hour duration
• PT1M is one-minute duration
• The T represents the Time-designator
• P1M is one-month duration

List Rows with SkipToken

We reuse the List Rows action but this time with the SkipToken variable

Update SkipToken

Before proceeding to the action on my records, I update the variable SkipToken with the value just retrieved, meaning my SkipToken variable is decrementing now (avoiding to retrieving the same set of 5000 records again and again and again)

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

Actions on this iteration

Here we can put our business logic 👇

And you’re done !

If you have any questions about Power Automate, please feel free to reach out