Overview
Power Automate has limitations when handling large datasets, especially with looping, API calls, and data retrieval from services like SharePoint, Dataverse, SQL, and Excel. To optimize performance, you need to use delegation, pagination, parallel processing, and efficient data filtering.
Work with large datasets efficiently
Overcome Power Automate limitations (5000-item limit, API throttling, timeouts)
Use batch processing, filters, and external storage
1️⃣ Challenges When Handling Large Datasets
🔸 Item Limitations → Power Automate has a 5000-record retrieval limit per action (e.g., SharePoint Get Items).
🔸 Looping Performance Issues → “Apply to each” can slow flows down with thousands of records.
🔸 API Throttling → Services like SharePoint, Dataverse, and SQL limit API calls per minute.
🔸 Timeout Errors → Long-running flows might fail if they exceed 90 minutes.
Example Problem:
- You need to retrieve 10,000 rows from a SharePoint list, but Power Automate only fetches 5000 by default.
- A loop over 10,000 records slows down execution and may exceed run limits.
2️⃣ Best Practices for Handling Large Datasets
Use Pagination to Retrieve More Records
Power Automate’s “pagination” allows fetching beyond the default 100 records for some actions.
Steps to Enable Pagination:
1️⃣ In Get Items (SharePoint), List rows (Dataverse), or other connectors, click Settings
2️⃣ Turn Pagination On
3️⃣ Set the Threshold (Max: 100,000 records)
Example: Fetch 20,000 SharePoint list items
- Without pagination → Retrieves only 100 items
- With pagination → Retrieves 20,000+ items in batches
Use OData Filters to Reduce Data Retrieval
Instead of retrieving all records, use OData filters to fetch only relevant data.
Example: Get only current month’s data from SharePoint
Created ge '@{formatDateTime(utcNow(), 'yyyy-MM-01T00:00:00Z')}'
✔️ Reduces API calls
✔️ Improves performance
Use Select and Filter Array to Reduce Looping
If you need only specific columns, use Select to reduce the data size before looping.
Example: Extract only “Email” from 100,000 contacts
1️⃣ Use “Select” to get only Email (instead of all columns)
2️⃣ Use “Filter Array” instead of “Apply to each” for better performance
✔️ Avoids unnecessary iterations
✔️ Reduces memory usage
Use Batch Processing (Parallel Execution & Chunking)
Large dataset operations should run in parallel to speed up execution.
Example:
- Instead of looping through 5000 records one by one, process them in batches of 500.
- Use “Concurrency Control” in “Apply to each” to enable parallel processing.
How to Enable Parallel Processing:
1️⃣ Click “Apply to each”
2️⃣ Open “Settings”
3️⃣ Turn on “Concurrency Control” and increase the degree of parallelism (e.g., 50)
✔️ Speeds up execution by 10x
✔️ Prevents timeout errors
Use External Data Processing (SQL, Azure, Power BI)
If Power Automate is too slow, move data processing to SQL, Azure, or Power BI.
Best Alternatives:
- SQL Stored Procedures → Process millions of records faster
- Azure Logic Apps → Handle high-volume workflows
- Power BI Dataflows → Pre-aggregate large datasets
✔️ Offloads heavy computation
✔️ Works for datasets with millions of records
3️⃣ Handling Large Data Sets for Specific Connectors
SharePoint (Overcoming 5000 Item Limit)
Use Pagination + OData Filtering
- Enable pagination in Get Items
- Use filters to fetch only required data
- Use “Top Count” to limit items
Dataverse (Fetching Large Records Efficiently)
Use FetchXML Query
- More efficient than OData for large datasets
- Supports advanced filtering & joins
Excel (Handling 100,000+ Rows Efficiently)
Use Excel Online – “List Rows Present in a Table”
- Enable Pagination for 100,000+ rows
- Use filters to get only necessary data
Alternative: Use Power Query in Power BI instead of Power Automate for huge Excel files.
SQL (Processing Millions of Rows)
Use SQL Stored Procedures
- Process data inside SQL instead of Power Automate
- Return only aggregated results