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:

Microsoft Dataverse list of Actions.

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

List rows in Dataverse. Settings for list rows threshold.

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.

Dataverse table, where to use the skip token.
  1. Initialize variable
    Initialize skip token area.
  2. List rows action from Dataverse connector:
    List rows of GL entries.
  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:Area to set skip token.
    
    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:
    Apply to each GL entries, select Value output.
  5. Add a condition if the variable is not empty:
    Condition of skip token added.
  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.Do this until the variable becomes empty for every 5,000 rows as a batch.
  7. Use the variable on the skip token to get the next 5,000 rows:
    Variable on the skip token 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):Set variable in loop.
    
    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: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.

Are You Receiving Our Newsletters?

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

Subscribe

Quarterly Newsletter Straight to Your Inbox

Subscribe