Optimizing Data Calls with Collections in Power Apps
Introduction
In Power Apps, frequent data calls to external sources like SharePoint, SQL Server, Dataverse, or REST APIs can cause performance issues, slow loading times, and excessive API requests. To improve performance, we use collections—temporary tables stored in memory—to optimize how data is retrieved and used.
This guide explains how to efficiently use collections in Power Apps for better performance, fewer API calls, and an improved user experience.
1. Understanding Collections in Power Apps
1.1 What is a Collection?
A collection is a temporary data storage table in Power Apps that holds multiple records and exists only while the app is running. It acts as an in-memory cache to avoid redundant API calls.
1.2 Why Use Collections?
✅ Improves Performance → Reduces data calls by storing frequently used data.
✅ Works Offline → Stores data locally, allowing offline access.
✅ Reduces API Call Limits → Prevents hitting service quotas.
✅ Faster Data Access → Access data from memory instead of external sources.
2. Creating and Using Collections
2.1 Creating a Collection
Use ClearCollect() or Collect() to create a collection.
Example: Storing SharePoint Data in a Collection
ClearCollect(EmployeeData, Filter(Employees, Status = "Active"))
🔹 What This Does:
- Retrieves only “Active” employees from SharePoint.
- Stores them in a collection named
EmployeeData. - Avoids fetching the same data repeatedly from SharePoint.
2.2 Using Collections in Galleries
Instead of retrieving data directly from an API, reference a collection.
Before (Bad Practice: Direct API Call in Gallery Items Property)
Items = Employees
🚨 Problem: Fetches data every time the gallery loads, causing delays.
After (Optimized: Use Collection Instead)
Items = EmployeeData
✅ Faster loading because the data is already stored in memory.
2.3 Updating Collections Without Extra API Calls
If a user updates a record, update the collection first instead of making an API call.
Example: Updating a Collection Instead of Recalling API
Patch(EmployeeData, LookUp(EmployeeData, ID = 101), {Status: "Inactive"})
✅ Updates the collection instantly without hitting SharePoint.
3. Reducing API Calls with Collections
3.1 Load Data Once on App Start
Instead of retrieving data every time a user opens a screen, load it once when the app starts.
Step 1: Load Data in OnStart Property
ClearCollect(AllOrders, Orders)
- Retrieves data only once when the app opens.
Step 2: Use Collection Instead of API Calls
LookUp(AllOrders, OrderID = 5001)
✅ No need to call the API again!
3.2 Lazy Loading with Pagination
Instead of retrieving thousands of records at once, load data in small chunks dynamically.
Step 1: Load First 100 Records
ClearCollect(OrdersCollection, FirstN(Orders, 100))
- Fetches only 100 records initially.
Step 2: Load More Data When Needed
Collect(OrdersCollection, FirstN(Skip(Orders, CountRows(OrdersCollection)), 100))
- Loads the next 100 records when a user scrolls or clicks “Load More”.
✅ Prevents API overload and keeps the app fast.
3.3 Caching API Data for Faster Access
If your app frequently calls an API, cache the response in a collection to avoid unnecessary calls.
Step 1: Retrieve API Data and Store in Collection
If(IsEmpty(APIDataCache), ClearCollect(APIDataCache, APIDataSource))
- Only calls the API if the cache is empty.
Step 2: Use Collection Instead of API
LookUp(APIDataCache, ID = 10)
✅ Faster, because data is retrieved from memory, not the API.
4. Efficiently Managing Collection Size
4.1 Removing Unused Data
Keep collections lightweight by removing unnecessary records.
Remove a Single Record
Remove(EmployeeData, LookUp(EmployeeData, ID = 101))
Clear Entire Collection When No Longer Needed
Clear(EmployeeData)
✅ Frees up memory and improves performance.
4.2 Keeping Collections Updated Without API Calls
If a user adds a new record, update the collection instead of making an API call.
Example: Adding a New Record to a Collection
Collect(EmployeeData, {ID: 102, Name: "John Doe", Status: "Active"})
✅ Data is updated locally without extra API calls.
5. Best Practices for Optimizing Data Calls with Collections
✅ Use Collections for Frequently Used Data → Reduce repeated API calls.
✅ Load Data Once and Reuse → Fetch data at app start instead of on every screen.
✅ Limit Collection Size → Use pagination and remove unnecessary records.
✅ Cache API Responses → Store API data in a collection to minimize API usage.
✅ Update Collections Instead of Fetching Data Again → Modify data locally.
✅ Use Lazy Loading → Load data in small chunks instead of all at once.
6. Conclusion
Using collections significantly optimizes Power Apps performance by reducing API calls, enabling faster data access, and providing offline functionality. By implementing best practices such as caching data, lazy loading, and updating collections instead of recalling APIs, you can build highly efficient apps that work seamlessly with large data sets.
Would you like examples for a specific data source like SharePoint, Dataverse, or REST APIs?