Power Automate | How to Get More Than 100,000 Rows From a Dataverse Table

Did you know that the list rows action from the Dataverse connector in Microsoft Power Automate has a limit on the number of rows that can be returned? The Dataverse connector returns up to 5,000 rows by default. Follow the below instructions to see how to get more than 5,000 rows:

To get more than 5,000 rows, turn on the Pagination and set the threshold up to 100,000 in Settings:

What if you have more than 100,000 rows to process? Here’s how you can get more than 100,000 rows from Dataverse table; use the skip token to send another request until the skip token returns empty.

1. Initialize variable
   
2. List rows action from Dataverse connector:
   
3. Set variable to skip token from @odata.nextLink value with the expression below. You need to replace both ‘List_rows_-_GL_Entries’ with your name of List rows action:

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

4. Insert/run first 5,000 rows:
   
5. Add a condition if the variable is not empty:
   
6. Do this until the variable becomes empty for every 5,000 rows as a batch. Set the count to 5,000. Enter timeout. PT1H is one hour.
7. Use the variable on the skip token to get the next 5,000 rows:
   
8. Set next skip token from @odata.nextLink value to the variable with expression below. You need to replace both ‘List_rows_-_GL_Entries_with_Skip_Token’ with your name of List rows action (previous step):

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

9. Insert/run returned list of rows:

And you’re done! If you have any questions about Power Automate, please feel free to reach out to us.