Power Automate | The Complete Guide to retrieve more than 5000 rows

Power Automate | The Complete Guide to retrieve more than 5000 rows

Retrieve more than 5000 rows using Power Automate

For many flows we need to retrieve more than 5000 records. Follow the guide and we will teach you, or get in touch with us : https://www.partnr365.fr/tma-support-evolution-continue

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

Power Automate Dataverse List Rows

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

Here what cross my mind :

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

  • Do I use 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

Power Automate Dataverse List Rows limitation 5000 records

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

Power Automate Dataverse List Rows Buffer Size Error

It fails \o/

Why is the buffer size error in List Rows ?

Because you exceeded the 100 MB limit

Buffer Size Error 100MB 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

Power Automate Dataverse List Rows Settings

Power Automate Dataverse List Rows Enable Pagination

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

Power Automate Dataverse List Rows more than 5000 records

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

Power Automate Dataverse List Rows no limit 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

Power Automate Dataverse List Rows limitation 100000 records

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

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

  • a @odata.nextLink parameter

  • and a batch of 5000 records :

Power Automate Dataverse List Rows odata nextLink

@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

Power Automate Dataverse List Rows skipToken

Prerequisites

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

List Rows with skipToken Initial Query

We get the odata.nextLink with an initial query.

Power Automate Dataverse List Rows skipToken inital 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='))))

Power Automate get 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

Power Automate Do Until SkipToken

List Rows with SkipToken

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

Power Automate Dataverse List Rows with SkipToken

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='))))

Power Automate Dataverse List Rows update SkipToken

Actions on this iteration

Here we can put our business logic 👇

image.png

And you’re done !

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