Excel + Power Automate: the 256‑row trap and 5 other real fixes

Scenario: the KPI email that lies

You’ve got a shared workbook in OneDrive for Business:

• File: SalesPipeline.xlsx
• Table: tblPipeline
• Columns: Region, Owner, Stage, Amount, CloseDate

Every morning, a cloud flow:

1. Reads all rows from tblPipeline.
2. Filters to deals closing in the next 7 days.
3. Aggregates by Region.
4. Sends a summary email to the regional leads.

It works during testing with a small dataset. Then the table grows past 300 rows. Suddenly, the email totals are lower than what people see in Excel.

Root cause: List rows present in a table returns a maximum of 256 items per call by default. Anything beyond that never reaches your filter or aggregation.

Let’s fix that properly, and then deal with the other common traps you’ll hit once the data and flows grow.

1. The 256‑row default: how pagination really works

When you add Excel Online (Business) → List rows present in a table in a cloud flow, the action has an internal page size limit. By default:

• Top parameter is empty (or not set).
• Pagination is off.
• The connector returns up to 256 rows.

That means:

• If your table has ≤256 rows, you get everything.
• If your table has >256 rows, you get the first 256 rows only.

This is not an error state. The action succeeds, and your flow continues with a truncated dataset.

Turning on pagination the right way

To get all rows:

1. Open your flow in the designer.
2. Select the List rows present in a table action.
3. In the action’s top‑right corner, open … (More) → Settings.
4. Under Pagination:
   • Enable Pagination.
   • Set Threshold to a value high enough for your table size, e.g. 5000.

Important details:

• The threshold is a maximum total items the action will retrieve across pages, not a page size.
• If your table has more rows than the threshold, the action stops at the threshold and still returns success.

In our pipeline scenario, if tblPipeline has ~1,200 rows, a threshold of 2000 is safe. Your daily KPI email now sees the full dataset.

When to leave the 256 limit in place

You don’t always want all rows. Keeping the default limit can be useful when:

• You are sampling data for a quick test.
• You only care about the top N items (e.g. first 100 newest records when sorted at source).

In those cases, explicitly set the Top parameter to your intended limit (e.g. 100) so that future readers of the flow understand that truncation is deliberate, not accidental.

2. Filtering at source vs. filtering after Excel

Once you turn on pagination, the next performance trap is pulling thousands of rows from Excel and then filtering in Power Automate.

In our scenario, you only need deals closing in the next 7 days. Pulling the entire table and then applying a Filter array action works, but it’s slower and burns more API calls than necessary.

Use OData filter in List rows

The Excel connector supports an OData Filter Query for List rows present in a table when the table is in:

• OneDrive for Business
• SharePoint document libraries

The filter runs on the service side, before rows are sent to your flow.

Example: only rows where CloseDate is within the next 7 days.

1. Add a Compose action to calculate the current date and the date 7 days from now using expressions:

formatDateTime(utcNow(), 'yyyy-MM-dd')

and

formatDateTime(addDays(utcNow(), 7), 'yyyy-MM-dd')

These return ISO‑formatted date strings that work reliably in OData comparisons when CloseDate is stored as a date in the Excel table.

2. In List rows present in a table, set Filter Query to something like:

CloseDate ge '@{formatDateTime(utcNow(), ''yyyy-MM-dd'')}' and CloseDate le '@{formatDateTime(addDays(utcNow(), 7), ''yyyy-MM-dd'')}'

Notes:

• The exact filter syntax must match your column names and data types.
• The dates must be in a format the connector can compare correctly (ISO yyyy-MM-dd is a safe choice for date columns).
• Expressions must use doubled single quotes inside the string literal, as shown above, to be valid in Power Automate.

This way, you only retrieve rows that match the date range, and pagination still applies if that subset exceeds 256.

When you can’t use Filter Query

Situations where you fall back to Filter array:

• Complex conditions involving multiple fields where the connector’s OData support is limited.
• Non‑date columns stored as text in Excel that don’t compare correctly via OData.

In those cases, you still want pagination on, but you accept that filtering happens in the flow. The key is to be explicit: document in a comment that filtering is downstream and pagination is required to avoid silent truncation.

3. Table structure and dynamic content: avoiding schema drift

Another frequent source of pain in Excel + Power Automate flows is schema drift: someone adds a column, renames one, or converts the table back to a range.

In our pipeline scenario, the sales team might:

• Add a Probability column.
• Rename Amount to DealAmount.
• Accidentally remove the table formatting.

Each change affects how List rows present in a table returns data.

Keep a stable table definition

Practical safeguards:

1. Lock down the table name

   • Use a clear, stable name like tblPipeline.
   • Avoid renaming tables once flows depend on them.

2. Avoid removing table formatting

   • If the range stops being a table, List rows present in a table fails.
   • Train users to add rows inside the table, not below it.

3. Treat column renames as breaking changes

   • Any rename changes the dynamic content names in the flow.
   • After a rename, re‑open the flow, re‑select the table, and update the actions that reference the old column names.

Using index‑based access when names change often

If you can’t fully control schema changes, you can reduce coupling by:

• Using Parse JSON with a schema that focuses on required fields only.
• Accessing fields via expressions like item()?['Amount'] instead of relying on dynamic content labels.

This doesn’t protect you from a column being removed, but it makes it easier to see and fix issues when they occur.

4. Concurrency and locking: multiple flows vs. one workbook

Excel Online (Business) is not a database. It has limited concurrency support, and multiple flows writing to the same workbook and table can cause:

• Conflict errors from the connector.
• Rows written by one flow overwriting or clashing with another.

In the pipeline scenario, you might have:

• One flow reading tblPipeline for the morning KPI email.
• Another flow appending rows based on form submissions.

Read‑only flows: keep them non‑blocking

For read‑only flows like the KPI email:

• Use List rows present in a table only.
• Avoid actions that modify the workbook in the same run.

Read operations are less likely to hit concurrency issues, but heavy load or frequent writes from other flows can still cause throttling.

Write flows: serialize updates

For flows that write to Excel:

1. Minimize the number of write actions

   • Batch logic in Power Automate and write once per run when possible.

2. Avoid parallel branches when writing to the same table

   • Sequential actions reduce the chance of conflict.

3. Consider moving writes to a more robust store

   • SharePoint lists or Dataverse tables handle concurrent writes better than Excel.

If you keep Excel as the store, treat it as a single‑writer system where flows are coordinated to avoid overlapping writes.

5. Alternatives when Excel is the wrong backend

The 256‑row trap is often the first sign that Excel is being used as a backend for things it wasn’t designed to handle.

In our scenario, once tblPipeline grows into the thousands of rows and feeds multiple flows, you start seeing:

• Slow runs when pagination pulls many pages.
• Occasional connector errors.
• Painful schema changes.

At that point, consider moving the core data to a store that Power Automate handles more naturally.

SharePoint list as a drop‑in upgrade

Pros:

• First‑class support in Power Automate.
• Built‑in column types for dates, numbers, choice fields.
• Better concurrency than Excel.

Migration pattern:

1. Create a SharePoint list SalesPipeline with columns matching tblPipeline.
2. Use Power Query or manual export/import to move existing rows.
3. Update your flow:
   • Replace List rows present in a table with Get items (SharePoint).
   • Use OData filters on CloseDate directly.

Excel can still be used as a reporting or ad‑hoc analysis front‑end via Get Data → From SharePoint List.

Dataverse for more complex scenarios

If your environment already uses Dataverse:

• Model SalesPipeline as a Dataverse table.
• Use Dataverse triggers and actions in Power Automate.

This adds more structure and governance at the cost of slightly more setup.

6. Testing and monitoring: catching truncation early

The worst thing about the 256‑row default is that it fails silently. You need patterns in your flows that make truncation obvious.

Add a row count check

In the KPI email flow, add a quick guard:

1. After List rows present in a table, add a Compose action with:

length(body('List_rows_present_in_a_table')?['value'])

2. Add a Condition:
   • If the length is exactly 256, send an alert email to the owner or post a message to a monitoring channel.

This doesn’t prove truncation (you might genuinely have 256 rows), but it tells you when you’re at the default limit and should double‑check pagination.

Use run history for spot checks

Periodically:

1. Open the flow’s run history.
2. Inspect a recent successful run.
3. Check the List rows present in a table output:
   • Confirm the number of items returned.
   • Confirm that the rows include the latest data visible in Excel.

If you see fewer items than expected, revisit pagination and thresholds.

One takeaway: make pagination a checklist item

Any time you add List rows present in a table in a production flow, treat pagination as a mandatory checklist item:

• Decide whether you need all rows or a subset.
• If you need all rows, turn on pagination and set a realistic threshold.
• Add a simple row‑count guard if the table is likely to grow.

That one habit prevents the Excel + Power Automate 256‑row trap from quietly corrupting your downstream logic.