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?
@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
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
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
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.
@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!!
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
@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.
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.