Unanswered

Extract more than 2000 rows from Excel

(0) Share

Report

Posted on by JuliosJamby

Hi, I'm trying to make my powerapps filter through more than 6000 rows in my excel file. However, it seems that the limit of the filter only stops till row 2000. How do I work around it?

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (0)

CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report

@JuliosJamby That's because of DELEGATION limit which exist for performance reasons. Therefore, you need to consider if you really need to load all the data. If for some reason you definitely need all the data, you have to get them in batches of 2K into collections and then combine them into one large collection.

Was this reply helpful? Yes No
JuliosJamby 54 on at

Like (0)

Report

@CNT able to show me how can I go about doing that?

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report

@JuliosJamby First, you'll need an ID column that goes from 1 to 6000 in your Excel Table.

Then do this,

ClearCollect(col1,Filter(yourExcelTable, ID>1 && ID<2001);

ClearCollect(col2,Filter(yourExcelTable, ID>2000 && ID<4001);

ClearCollect(col3,Filter(yourExcelTable, ID>4000 && ID<6001);

ClearCollect(colCombined, col1,col2,col3)

Was this reply helpful? Yes No
JuliosJamby 54 on at

Like (0)

Report

@CNT I get an error message saying "Delegation Warning. The AND operation is not connected by this connector. What should I do?

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report

@JuliosJamby It's not an Error but a Warning. Are you able to load the 6000 records in the combined collection?

Was this reply helpful? Yes No
JuliosJamby 54 on at

Like (0)

Report

@CNT Nope, nothing appeared in the gallery

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report

@JuliosJamby Can you show me the formula and a screen shot of the gallery.

Was this reply helpful? Yes No
JuliosJamby 54 on at

Like (0)

Report

@CNT

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report

@JuliosJamby Does this column called Row have numbers from 1 to 6000 in the Excel table? It would be better that it is formatted as Number and not as Text.

Was this reply helpful? Yes No
AmeerJab 370 on at

Like (0)

Report

ofcourse it won't work, when you connect to your dataset through powerapps connectors you will only get 2000 rows, in this case you will only get the first 2000 records, you can solve this by using custom API's or using power automate to connect to your data.

Was this reply helpful? Yes No