Answered

Retrieving more than 2000 row records from Excel stored in OneDrive for Business

(0) Share

Report

Posted on by Hor

I have a powerapp connected to an Excel table (4k rows) and the Excel file is stored in onedrive for business. The table has three columns, Name, Code and ID(1-4000). Understand that PowerApp could only retrieve first 2000 rows of records from the Excel file, and I have seen some workarounds that use the Collect function, somehow I couldn't get it work.

Basically, I insert the following function at App start:

ClearCollect(Record1, Filter(Table1, ID<2000));ClearCollect(Record2, Filter(Table1, ID<4000 And ID=>2000));Collect(AllRecord,Record1, Record2)

However, when I view the collection items , it seems that any rows beyond ID 2000 was not collected. Anyone could help please.

Categories:

Building Power Apps

All responses (6)

Answers (1)

Sort by

Verified answer

Pstork1 69,661 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

If you don't need to write changes back to the Excel file then you can Import the file instead of using it as a direct data source. That will avoid the lack of delegation support in Excel. Other than that there really is no way to do it since any filter you use would need to be delegable and Excel doesn't support delegation of any kind. The only real solution is to use something else like SharePoint, SQL or CDS as the data source.
Hor 38 on at

Like
a
(0)

Report
Copy link

Link copied!

@Pstork1

Yes currently the only way for me is to import the Excel file. However, I was just curious seeing some solutions provided in other threads below.
https://www.voksedigital.com/solving-delegation-limit-issue-in-power-app-for-2000-records/
https://powerusers.microsoft.com/t5/Building-Power-Apps/ClearCollect-for-more-than-2000-records/td-p/145718
https://powerusers.microsoft.com/t5/Building-Power-Apps/How-can-i-manage-to-filter-more-than-2000-records/m-p/264784

They seemed to discover a workaround to call every 2000 rows separately into the app. I followed their steps yet it didn't work.
Pstork1 69,661 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

The first link is about this problem in SQL and the second is SharePoint. Both of those support delegation and so the solutions will work with them. The third one says it will work in Excel, but it is incorrect since Excel doesn't support delegation. I assume the person who supplied the answer didn't actually test it in Excel.
Hor 38 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes I guess that's the answer, the Filter function would not work on non delegable datasource, Excel in Onedrive in this case. Thank you for your clarification !
Rubenmaximus 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi, My question is the opposite. Can we fill more than 2000 responses through Poweraaps?

I have created an app where user submits data n data gets saved in Onedrive excel.

Im not going to view any data in Poweraaps. Just use Poweraaps as interface/frontend.
Pstork1 69,661 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

For most of the data sources you can definitely add records to a data source that has over 2,000 records. But I don't think you will be able to do that for Excel in OneDrive. The size of the table you can create is limited. Excel is different from a SharePoint list or a SQL database.