Overcoming the 2,000-Row Limit in PowerApps with ClearCollect

The ClearCollect function is a widely used tool in PowerApps to gather data from multiple sources, such as SharePoint, SQL databases, and Excel tables. However, as many developers know, PowerApps imposes a 2,000-row limit on data retrieval. This can be frustrating when working with large datasets that need to be displayed or manipulated within the app.

To bypass this limitation and retrieve more than 2,000 rows of data, you can combine ShowColumns() and Filter() functions. Here’s an example of how this can be done:

ClearCollect(
    collection,
    ShowColumns(
        Filter(complex_data_set, Row_Id <= 2000),
        "column1", "column2", "column3"
    ),
    ShowColumns(
        Filter(complex_data_set, Row_Id > 2000 && Row_Id <= 4000),
        "column1", "column2", "column3"
    )
)        

While this approach works, one major downside is performance degradation as the data size increases. For instance, when collecting 100,000 rows, it can take a long time—up to 110-120 seconds in my own experience. So, while functional, this method is less optimal for large-scale data.

Improving Performance: Using Concurrent and Collect

The primary issue with the ClearCollect method above is performance. Since the data is collected in sequential batches of 2,000 rows, each batch must finish before the next one starts. This delay can be significant when dealing with larger datasets.

To tackle this, we can leverage the Concurrent() function, which allows multiple functions to run in parallel. This way, we can collect data more efficiently by processing multiple batches simultaneously. Here’s how you can implement it:

Concurrent(
    Collect(collection1, ShowColumns(Filter(complex_data_set, Row_Id <= 2000), "column1", "column2", "column3")),
    Collect(collection2, ShowColumns(Filter(complex_data_set, Row_Id > 2000 && Row_Id <= 4000), "column1", "column2", "column3"))
)        

In this method, we use the Collect() function for each data batch, ensuring each batch runs in parallel. Afterward, we can merge the collections into a single collection:

ClearCollect(collection_all, collection1, collection2)        

Finally, it’s essential to clean up by clearing the intermediate collections to free up memory:

Clear(collection1); Clear(collection2)        

The advantage of using Concurrent() is that the data collection process is much faster, since the different collections are created at the same time rather than sequentially. This reduces overall processing time significantly.

Performance Comparison: ClearCollect vs. Concurrent

To illustrate the performance improvements, I conducted a test using a 20,000-row dummy dataset (from Maven Analytics). The results were clear:

• ClearCollect() (sequential processing) took significantly longer to collect data.
• Concurrent() (parallel processing) was much faster, processing all batches simultaneously and reducing the total time taken.

You can see the results in action in the comparison gif below:

• The left button demonstrates the ClearCollect() approach.
• The right button demonstrates the Concurrent() and Collect() approach.

Conclusion: Unlocking PowerApps' Full Potential

While the 2,000-row limit in PowerApps can be restrictive, there are efficient ways to bypass this limit and work with large datasets. By utilizing functions like ClearCollect(), Concurrent(), and Collect(), along with proper filtering and column selection, you can significantly improve your app's data handling capabilities.

Whether you're a seasoned PowerApps developer or just getting started, mastering these techniques will help you build more powerful, scalable applications without the constraints of the 2,000-row data limit.

Ready to take your PowerApps development to the next level? Let’s break through the barriers and create better, faster apps today!