Greetings
Hey everyone, hope you’re all doing well. I wanted to chat about something pretty exciting today—my recent experience with handling large SharePoint lists in Power Apps. Let me share what I’ve learned and how it can help you tackle similar challenges.
Prologue
Working with SharePoint as a data source in Power Apps is a fundamental skill that most app makers have encountered. It’s usually straightforward and smooth when dealing with small lists, say between 100 to 1,000 records. However, the game changes significantly when you’re working with larger datasets, like 5,000 records or even up to 100,000 records. That’s where things get more challenging and interesting.
In this blog, I will use real dataset of my recent project (All sensitive data are masked and replaced with sample data)
This dataset contains 131,716 records.
What’s the problem?
The answer is simple. “Performance”
As you’ve probably noticed, displaying data from large SharePoint lists in Power Apps can be quite slow. This delay becomes more pronounced as your dataset grows or if you have a lot of columns.
But why? why that slow?
You will get answer if you open the Monitor and see how Power Apps trying to fetch the data.
Monitor is tell us tht Power Apps can’t receive all record within one request, So it need to run getMoreRows until it’s get all record that matched with filter condition, This mean it will take longer as our dataset grow up.
Solution #1: Indexed column
The reason Power Apps can’t retrieve all records in one request is because of the SharePoint REST API’s limitations. Behind the scenes, when you use the Filter() function, Power Apps sends a filtering request to the SharePoint REST API. However, filtering with non-indexed columns is not supported for lists that have more than 5,000 items.
Therefore, Power Apps has to resort to fetching every item in the list and then applying the filter by Power Apps itself.
So, we can help SharePoint by indxed all column we want to filter.
1. Go to List settings
2. Scroll down and click on Indexed columns
3. Create index
4. Wait for SharePoint to indexed your column (This operation may take a while depends on your data)
Once a column is indexed in SharePoint, filtering operations can become significantly faster. With indexing in place, filtering can often produce results almost instantly because only a single request is needed to retrieve the filtered data. This improvement in performance highlights the importance of proper indexing for efficient data retrieval and filtering in Power Apps.
Solution #2: Rearrange filter criteria
Creating indexed column can make filtering faster, but there are some scenarios that its not enough
Take a look at this, I’m trying to filter with 2 condition: Items that belongs to company “GRBK” and ReceiptId is “RCP-8754”
Even when both columns used in the filter condition are indexed, filtering can still take time because Power Apps needs to process the filter criteria sequentially.
For example, if Power Apps first filters for items with CompanyCodeTxt = “GRBK” and this initial filter results in more than 5,000 items, it can’t process them all in one request. This necessitates the use of iterative operations like getMoreRows to retrieve and process the filtered data in batches.
The solution is straightforward: rearrange your filter conditions so that the most specific one comes first.
You’ll notice a nearly instant result when filtering by ReceiptId because it’s a more specific criterion compared to filtering by Company.
Conclusion
While SharePoint may not be the most ideal data source for handling large datasets, there are several optimizations you can implement to achieve better performance. Utilizing indexed columns and optimizing filtering criteria can significantly enhance the performance of your Power Apps solutions. These strategies help minimize the impact of SharePoint’s limitations with large datasets and improve overall app responsiveness.
