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:

Power-Automate-Dataverse

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

Power-Automate-Dataverse Power-Automate-Dataverse

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.

Power-Automate-Dataverse
  1. Initialize variable
    Power-Automate-Dataverse
  2. List rows action from Dataverse connector:
    Power-Automate-Dataverse
  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:Power-Automate-Dataverse
    
    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:
    Power-Automate-Dataverse
  5. Add a condition if the variable is not empty:
    Power-Automate-Dataverse
  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.Power-Automate-Dataverse
  7. Use the variable on the skip token to get the next 5,000 rows:
    Power-Automate-Dataverse
  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):Power-Automate-Dataverse
    
    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:Power-Automate-Dataverse

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

Are You Receiving Our Newsletters?

Subscribe to receive our monthly newsletters with the latest updates all in one place! Get important product information, event recaps, blog articles, and more.

Subscribe

Monthly Newsletter Straight to Your Inbox

Subscribe