I was curious about the detailed behavior of the “List Rows Present in a Table” action that retrieves multiple data (rows) from Excel tables, so I investigated various aspects.
Action “List Rows Present in a Table”
The Excel Online action “List Rows Present in a Table” is an action that retrieves multiple data (rows) from tables in Excel.
In this article, I’ve examined the detailed behavior of this “List Rows Present in a Table” action.
Preparation
For this investigation, I created tables in Excel on SharePoint Online (SPO) with
data containing “500 rows,” “10,000 rows,” and “100,000 rows,” and performed various operations on these tables.
Filter Query
First, let’s look at the filter query capabilities of this action.
Available Operators
The operators available for filter queries in the “List Rows Present in a Table” action are as follows:
- eq : equals
- ne : not equals
- startswith : starts with
- endswith : ends with
- contains : contains
Filter queries like “eq” for columns work without issues,
However, comparison operators like greater than or equal to (ge), less than or equal to (le) result in errors. *Note that logical operators like “and” and “or” also cannot be used.
While it’s helpful that “contains” can be used, filtering for “dates” is quite inconvenient.
Filtering by Date
When filtering columns containing dates, you need to select either “Serial Number” or “ISO 8601” as the DateTime format. You can filter using serial values,
Or using ISO format.
The “serial value” of Excel date data is Excel’s mechanism for managing dates and times as numerical values. The reference date is January 1, 1900, which has a serial value of “1,” and the serial value increases by 1 for each passing day.
However, as mentioned earlier, the inability to use comparison operators is quite inconvenient. I tried to filter at the month level using “startswith,” but unfortunately, it doesn’t work.
Therefore, if you want to apply comparison filters for dates (like “after February 1, 2025”), you need to first retrieve all data and then filter it using the “Filter Array” action.
However, as I’ll explain later, the default number of rows that can be retrieved from Excel is limited to 256 rows. Similar to Power Apps’ delegation and data row limitations, you need to consider this limit to avoid unexpected behavior. *Note: In the sample, although data after January 1, 2026 exists, none of it was retrieved.
Number of Retrievable Rows
Next, let’s discuss the number of data rows that can be retrieved with this action.
Default is 256 Rows
Without any special configuration, the maximum number of data rows that can be retrieved with this action is 256.
If you want to increase this limit, from [Settings],
Turn on [Pagination] and set a [Threshold] value,
This allows you to retrieve more than 256 rows of data.
Maximum is Supposedly 100,000, However…
Technically, the upper limit for the [Threshold] is set at 100,000 rows,
But when I tried to retrieve 100,000 rows of data,
It never succeeded, which might be due to my environment… *I believe it shouldn’t exceed the 25MB data limit since it contains only minimal text
For reference, when retrieving 5,000 rows,
It takes about 45 seconds. Note that even with 5,000 rows, failures can occur.
Filtering is Performed on the Excel Side (Delegation is Possible)
Similar to Power Apps delegation, it seems that the filtering process itself is performed on the Excel side. Even when filtering with [Threshold] turned off (256 row limit),
Data beyond the 256th row is still properly retrieved.
Excel Settings Affect the Rows Retrieved
It’s not widely known, but manipulating the table in Excel can change the results of this action.
For example, when executing the action without making any changes to the Excel table,
Data is retrieved normally,
However, if you sort the data in Excel and then execute the action again,
The retrieved results are also sorted.
However, it appears that Excel-side filters are not applied. Even when filtering for values below 450 in Excel and executing the action,
The results remain unchanged.
After examining these various behaviors in detail, it seems that for stable handling of large datasets in Excel, using Office scripts might be a better approach.
*Of course, using SPO or Dataverse in the first place might be even better.
コメント