PowerApps can't get more than 2000 rows from excel file stored in Sharepoint as datasource.

(0) Share

Report

Posted on by Community Power Pla...

Input : I have excel file of 8 MB with 1 lakh records stored in Sharepoint as a datasource

Requirement : To fetch records from above excel file based of the filter query.

Problem :

In PowerApps every data source (SharePoint, Common Data Service, OneDrive) is under limitation of 2000 items. It means you cannot get more that 2000 items from a data source but even more than that – PowerApps won’t even “be aware” of any rows above 2000.

Is there any way PowerApps know more than 2000 items from excel stored in Sharepoint?

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Pstork1 68,697 Most Valuable Professional on at

Like (1)

Report

Excel as a data source doesn't support any delegated functions, so there is no way to retrieve more records than whatever the data row limit is set to. That's 500 by default and 2,000 as a maximum. Using delegable functions in data sources that support them like SharePoint and Dataverse you can access more than 2,000 items.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (1)

Report

@Anonymous

Up to 15000 records can be retrieved as a single static excel file but the data cannot be modified. All PowerApps functions can be used to search the data without delegation restrictions. It may be useful in certain limited situations as a reference table.

Was this reply helpful? Yes No
Verified answer

v-xiaochen-msft on at

Like (2)

Report

Hi @Anonymous ,

If you have a large amount of data, you may only be able to use flow to get them.

Besides, although you can get them, it will waste a lot of time.

For example:

I have a table in excel and it has 16000+ rows.

The flow took 1-2 minutes.

So, my suggestion is to put your data into sql server and create a view.

Best Regards,

Wearsky

Was this reply helpful? Yes No
MustafaHussain 173 on at

Like (0)

Report

@Anonymous @v-xiaochen-msft

well, If I use this flow and if I am using excel as a DataSource how can I then respond back to PowerApps with the items of flow and store them in a collection??

Help!!

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (1)

Report

For that you need the HTTP Response action as the last thing in the flow. That will let you return the array of items to Power Apps and capture it in a collection. Be aware that HTTP response is a Premium action.

Was this reply helpful? Yes No
RohanShaw 14 on at

Like (0)

Report

@Drrickryp
Is the 15K limit to static data per table or across the entire sheet?
I have a list of 20K post codes and suburbs and I was thinking of splitting them into multiple tables on the one sheet.

Thanks

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (1)

Report

@rohan2

Yes, split into separate tables and then combine them in a collection. See how I did it here https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Automatically-Prefill-City-and-State-using-Zip-Codes-in-your-App/ba-p/200465

Was this reply helpful? Yes No
RohanShaw 14 on at

Like (0)

Report

@Drrickryp Thanks that is perfect!!
Love the blog post.
https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Automatically-Prefill-City-and-State-using-Zip-Codes-in-your-App/ba-p/200465

Was this reply helpful? Yes No