Power Automate enforces limits on the number of items a flow can process in a single execution. When a flow tries to handle more items than allowed, it fails with a maximum item count exceeded error.
This guide explains:
Why this error occurs.
How to check and monitor item limits.
How to optimize your flow to process large datasets efficiently.
Step 1: Understanding Maximum Item Count Limits in Power Automate
1.1. Limits on the Number of Items Processed
The maximum number of items a flow can handle depends on the connector and licensing plan. Below are some common limits:
| Connector | Item Limit per Flow Execution | Notes |
|---|---|---|
| SharePoint (Get Items) | 5,000 items (default), 100,000 items (with pagination) | Can be increased using pagination settings |
| Dataverse (List Rows) | 5,000 items (default), 100,000 items (with pagination) | Use filtering to retrieve only necessary data |
| SQL Server (Get Rows) | 500,000 items (depends on SQL query performance) | Best practice: Use pagination and TOP clause |
| Excel (List Rows Present in Table) | 256 rows per call (Standard), 100,000 rows (Premium) | Best practice: Store large datasets in Dataverse or SQL |
- Loops (“Apply to Each”) also contribute to item count limits.
- If your flow processes more than the limit, you must batch the data or use pagination.
Step 2: Identifying the Issue
2.1. Check Flow Run History for Errors
- Open Power Automate (https://make.powerautomate.com).
- Click on My Flows → Select the affected flow.
- Go to Run History → Open a failed run.
- Look for an error message such as:
The number of items in the query result exceeds the limit.
2.2. Identify Which Action Is Exceeding the Limit
- Expand each action in the failed run.
- Find the step with a warning or failure related to item count limits.
- Check the number of records retrieved in connectors like SharePoint, Dataverse, SQL, or Excel.
Step 3: Resolving the Issue
3.1. Enable Pagination to Increase Limits
For connectors like SharePoint, Dataverse, and SQL, enabling pagination allows handling up to 100,000 records.
Enable Pagination in Get Items (SharePoint)
- Open the Get Items action.
- Click Settings → Turn on Pagination.
- Set the Pagination Limit (e.g., 50,000).
3.2. Use Filtering to Reduce the Number of Items
If your flow retrieves too many records, use filtering to fetch only the necessary items.
Filter Query for SharePoint (Get Items)
Instead of fetching all items, use:
Filter Query: Status eq 'Active'
This retrieves only active items, reducing the dataset size.
Filter Query for Dataverse (List Rows)
Filter Query: createdon ge 2024-01-01
This retrieves only recent records.
3.3. Use “Top Count” to Retrieve Only Necessary Items
Most connectors allow limiting the number of records with a Top Count parameter.
Example: Limit SharePoint Get Items to 500 Records
- Open Get Items in SharePoint.
- Click Advanced Options → Set Top Count to
500. - This ensures the flow retrieves only the first 500 items.
3.4. Process Data in Batches Instead of All at Once
If a flow processes thousands of items at once, break it into smaller batches.
Use Skip and Top in Power Automate
To process data in chunks (e.g., 1000 records per run):
- Use
Top Count=1000. - Add a Loop with a counter variable.
- Use the Skip function to retrieve the next batch in the next loop iteration.
3.5. Optimize “Apply to Each” Loops
Large loops slow down execution and may hit item limits.
Enable Parallel Processing in Loops
- Open the Apply to Each action.
- Click Settings → Enable Concurrency Control.
- Set Degree of Parallelism (e.g.,
20).
3.6. Move Large Datasets to Dataverse or SQL
If your flow retrieves large Excel or SharePoint datasets, consider:
✅ Storing the data in Dataverse or SQL instead.
✅ Running queries on SQL rather than processing large lists in Power Automate.
3.7. Upgrade to a Higher Power Automate Plan
If your automation regularly exceeds limits, consider upgrading to a Per-User or Per-Flow Plan for higher limits.
- Open Microsoft 365 Admin Center (https://admin.microsoft.com).
- Navigate to Billing → Purchase Services.
- Choose Power Automate Per-User Plan or Per-Flow Plan.
- Assign the new plan to the user or flow.
Step 4: Advanced Troubleshooting
4.1. Use Azure Logic Apps for Large Data Processing
If Power Automate is not sufficient for handling large data, consider using Azure Logic Apps, which support:
✅ Larger dataset processing
✅ Better scaling and control
4.2. Use Power Automate RPA for Excel Processing
For large Excel datasets, use Power Automate RPA (Robotic Process Automation) to process files without hitting API limits.
Step 5: Preventing Future Issues
5.1. Best Practices for Handling Large Datasets
Use pagination – Set a limit to handle large datasets efficiently.
Filter data before retrieval – Use Filter Query to retrieve only required items.
Process data in batches – Avoid retrieving and processing all data at once.
Use SQL or Dataverse for large datasets – Avoid using SharePoint or Excel for big data operations.
Monitor flow usage – Regularly check Power Platform Admin Center for trends.
5.2. Set Up Flow Monitoring and Alerts
- Go to Power Platform Admin Center.
- Set up alerts for large dataset usage to detect potential overuse early.