Power Automate: Retrieving More than 256 Rows from an Excel Table

Pulling data out of tables in Excel is a great capability in Power Automate flows. We often use it to load SharePoint lists as part of ongoing processes (monthly, quarterly, annually, etc.) Many times we build these flows with a small amount of data in Excel, but then over time the data grows, and we hit a threshold. The hard part is often realizing this has happened to you. It happened to me recently, so I figured I’d share how to solve it.

We had something like 275 rows in the table after a while, and that number is going to continue to grow. We knew we weren’t getting all the rows from the Excel table in the flow, but we didn’t know why. For some reason, we were only getting 256 rows. When I saw the number 256, I knew it had to be some sort of limitation, since 256 is 2^8. Powers of two limitations almost always mean a poor architecture choice by the creator of the tool, IMO, 256 is just way to low a limit for this particular thing; some higher power of 2 would have been a much better idea.

If you look at the documentation for the List rows present in a table action (which is in the Excel Online (Business) group of connectors, you won’t find anything obvious. I used my trusty Ctrl-F and searched for 256. Voila!

In the section Known issues and limitations with actions of this documentation page, buried in a table, you’ll find the following:

Pagination

List rows present in a table

The List rows present in a table action returns up to 256 rows by default. In order to get all rows, turn on pagination.

The key information is in the right column. By default, you’ll only get the first 256 rows from the table. In testing, that may be more than adequate. But as your solution scales, you probably will want to ingest more rows than 256. See my comments above.

The fix is simple, once you know what it is. In the List rows present in a table action, click on the ellipses and then Settings.

Right at the top of the Settings, you’ll see that Pagination is off.

Simply turn on pagination and set the threshold number. Once you’ve done this, the connector will retrieve the rows in the Excel table page-by-page (the size of the pages is not defined in the documentation) until it has all the items OR it reaches the threshold value. This is spelled out in a separate document in the Pagination support section.

Your threshold value can be from 0 to 100000, and you have to provide it. I see no reason not to use 100000, unless a lower number makes better sense for your business logic.

In our case, the number of rows probably will always be in the hundreds, but more than 256!