Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

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

(0) ShareShare
ReportReport
Posted on by

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:
  • RohanShaw Profile Picture
    RohanShaw 14 on at
    Re: PowerApps can't get more than 2000 rows from excel file stored in Sharepoint as datasource.

    @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

  • Drrickryp Profile Picture
    Drrickryp on at
    Re: PowerApps can't get more than 2000 rows from excel file stored in Sharepoint as datasource.

    @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 

  • RohanShaw Profile Picture
    RohanShaw 14 on at
    Re: PowerApps can't get more than 2000 rows from excel file stored in Sharepoint as datasource.

    @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

  • Pstork1 Profile Picture
    Pstork1 64,703 on at
    Re: PowerApps can't get more than 2000 rows from excel file stored in Sharepoint as datasource.

    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.

  • MustafaHussain Profile Picture
    MustafaHussain 173 on at
    Re: PowerApps can't get more than 2000 rows from excel file stored in Sharepoint as datasource.

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

  • Verified answer
    Re: PowerApps can't get more than 2000 rows from excel file stored in Sharepoint as datasource.

    Hi @Anonymous ,

     

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

    vxiaochenmsft_0-1633314391971.png

    vxiaochenmsft_1-1633314418877.png

     

    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.

    vxiaochenmsft_3-1633314638489.png

     

    The flow took 1-2 minutes.

    vxiaochenmsft_2-1633314620740.png

     

     

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

     

    Best Regards,

    Wearsky

     

     

  • Drrickryp Profile Picture
    Drrickryp on at
    Re: PowerApps can't get more than 2000 rows from excel file stored in Sharepoint as datasource.

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

  • Pstork1 Profile Picture
    Pstork1 64,703 on at
    Re: PowerApps can't get more than 2000 rows from excel file stored in Sharepoint as datasource.

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,343

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,703

Leaderboard