Load Large Dataset in Canvas App – The Right Way!
📌 Let's understand the problem first
Before we dive in, let’s address the elephant in the room 🐘—you’ve got 50,000 records in an external data source like SharePoint or Dataverse, and you’re wondering:
❓ Do we need to load large dataset in Canvas App collection? ❓ If we do, won’t it affect performance?
The answer is a big YES—loading massive amounts of data directly into a collection can cause serious performance issues! 🚨
💡 Why?
Once data is in a collection, the app has to store and handle queries, which demands a lot of processing power ⚡.
Retrieving large datasets from servers takes time ⏳—sometimes 30 sec to 1 min depending on the size.
✅ The Smart Solution
Instead of loading everything at once, use delegable queries like Filter, Sort, LookUp to only fetch the data that’s needed at that moment.
🚀 Why does this improve performance?
Canvas App doesn’t process queries itself—the data source handles the processing, reducing app workload.
Once the data is processed, only the required records are sent to the app 📥, boosting performance.
⚡ But Wait... What If You Still Need Large Data?
I totally get it—sometimes, even after filtering, your dataset still exceeds 2000+ records. What do we do then? 🤔
💡 That’s where these two concepts come in:
1️⃣ Delegation – Allowing the data source to process queries instead of the Canvas App.
2️⃣ Pagination – Loading data in batches rather than all at once.
By combining delegation & pagination, you efficiently handle large datasets without compromising performance. 🚀
Loading large Dataset to Canvas App Collection
🤔 Let's first understand how Canvas App load data to collection
Collections in Canvas Apps are like local mini-databases 📂 that store data temporarily. You can create collections using Collect() or ClearCollect(), with the syntax:
Collect(collectionName, Record or Table)
ClearCollect(collectionName, Record or Table)
Everything sounds straightforward, right? Well, let’s dive into the real challenge!
📚 Sample Data for Practice
To practice click below to get sample data in Excel import that data to your SharePoint
Confused how to import data from Excel to SharePoint? – Click here
🔎 The Experiment: Loading 4999 Records from SharePoint
In this scenario, we have a SharePoint list (tbl_5000_products) containing 4999 records.
Let’s attempt to load all records into a Canvas App collection and see what happens.
//Button -> OnSelect Property
Collect(col_5000_Products, tbl_5000_products)
//Label -> Text Property
//Below function will give the count of rows/ records getting loaded in collection
CountRows(col_5000_Products)
But—wait for it—❌ only 500 records got loaded. 🤯
Why Did This Happen❓
To answer that, let’s first talk about Delegation and Delegable Functions!
👀 Delegation is how Canvas Apps avoid processing data locally—it lets the data source handle queries instead.
⚡ Delegable functions (like Filter, Sort, etc.) help push data processing to sources like SharePoint, which improves performance.
💡 The Issue?
ClearCollect() isn’t delegable, meaning SharePoint checks the app’s Data row limit—default 500—and only returns that many records.
🚨 Can We Increase the Data Row Limit of App?
Well, not really. The Data Row Limit in Canvas Apps ranges only from 1 to 2000
So, let’s increase the row limit to 2000 first. That still doesn’t get us all 4999 records. How do we handle the rest? 🤔
💡Solution: Loading Data in Batches of 2000 Records in Collection📦
Since SharePoint can only send 2000 records at a time, we use batch loading! 🎯
How?
1️⃣ We filter the dataset into smaller chunks using the Filter() function, which is delegable!
2️⃣ Instead of loading everything, we grab data in subsets—for example, filtering records by Product Company column, which has following values "Acer", "Nvidia", "Dell", etc.
Below is the screenshot of SharePoint list (tbl_5000_Product)
In Canvas App – Update the following code in Button where collection code is defined.
//ClearCollect function cannot be written inside ForAll
//Case 1: If Company Name columns is of type text in the backend, then we hae to manually create an array or table of distinct companies
Clear(col_5000_Products);
ForAll(["NVIDIA", "Dell", "Apple", "BenQ", "Logitech", "Razer", "AMD", "SteelSeries", "ASUS", "Intel", "Corsair", "LG", "Epson", "Samsung", "HP", "Canon", "Brother", "Lenovo", "Acer"] As X,
Collect(col_5000_Products, Filter(tbl_5000_products,'Product Company'.Value=X.Value))
)
// Case 2: If your Company Name column is of Type Choice, then use Choices Functions instead of giving values manually
Clear(col_5000_Products);
ForAll(Choices(tbl_5000_products.'Product Company') As X,
Collect(col_5000_Products, Filter(tbl_5000_products,'Product Company'.Value=X.Value))
)
🎉 Woo-hoo! We did it! 🚀 All the data is finally loaded!
This is a huge win! 🎯 No more struggling with row limits, no more incomplete data—just smooth and optimized data retrieval in your Canvas App! 🏆
Time to celebrate the success! 🥳 Your app is now handling large datasets like a pro! 🚀🔥
⚠️ Important Considerations
✅ This works only if each subset has fewer than 2000 records. ⚠️ If a company has more than 2000 records, it won’t load all—so, refine filters with more columns!
📌 Example: Instead of just filtering by Product Company, add another column like Product Category, Manufacturing Year etc. to get smaller subsets.
🚨 Is there any other ways to load all data?
Yes! While the batch filtering approach is the simplest and most optimized way to get all your data, there are other smart methods too! 🎯
🔄 Alternative Approaches to Load Large Datasets:
💡 1️⃣ Index-Based Filtering Approach
Create a Custom Number Type Column in your list and name it Index_ID.
Each record gets a unique & non-empty value (ex:
1, 2, 3, 4...).In Canvas App, use a filter on Index_ID to progressively retrieve all records.
✅ Pro Tip: This ensures structured data retrieval without hitting delegation limits!
- 😫 Cons: Maintaining Index_ID column is a challenge.
⚡ 2️⃣ Power Automate + API Approach
Leverage Power Automate Flow to make a custom API call to External data source
The API will fetch all records without delegation restrictions.
- We then will send the data back to Canvas App from Power Automate in JSON Format.
✅ Pro Tip: This method is great for scenarios where you need complete dataset retrieval at once without pagination!
- 😫 Cons: Its slow and takes a lot of time, require knowledge on working with APIs.
💡Now what's Next?
Now that you know how to load all data to the collection in Canvas App, you might be wondering what is the best way to show this data, this is where Pagination comes in picture. Don’t waste time explore and learn more about pagination and how to implement pagination in Canvas App.
Click on the below Image and Explore more about Pagination….
[…] thing first, load all the data to Collection. You ask how?Click here to open the blog post where I have shown how to to load all data to […]