Powerapps overcome 2000 item limit from any datasource

In Power Apps, delegation refers to whether a data processing task (like filtering, searching, or sorting) is performed by the data source (server-side) or by Power Apps itself (locally on the device). 

Delegable Functions

These functions allow the data source to handle the heavy lifting. The app sends the query to the server, and only the results matching the criteria are returned to the device. This is essential for performance and accuracy when working with datasets exceeding 2,000 records. 

• Filter & LookUp: Delegable for most tabular data sources (Dataverse, SQL, SharePoint).
• Sort & SortByColumns: Generally delegable, but often restricted to single columns or specific data types.
• StartsWith: Frequently delegable and often used as a performance-friendly alternative to Search.
• Sum, Average, Min, Max: Delegable on advanced data sources like SQL and Dataverse, though support varies by connector.
• Logical Operators: And (&&), Or (||), and comparison operators (=, <, >) are typically delegable. 

Non-Delegable Functions

If a function is non-delegable, Power Apps must download a subset of the data to the device and then process it locally. 

• Data Row Limit: By default, Power Apps only retrieves the first 500 records for local processing (this can be increased to 2,000 in app settings). Records beyond this limit are ignored.
• Common Examples:
  • Search: Non-delegable in SharePoint, but delegable in Dataverse.
  • Group / GroupBy: Generally non-delegable.
  • FirstN, Last, LastN: Processed locally.
  • ForAll: Does not support delegation.
  • String Functions: Lower, Upper, Mid, Len, and Concatenate often block delegation in complex filters.
  • Calculated Columns: Using logic on columns that require local calculation usually triggers a delegation warning. 

Why This Matters for Your Interview

• Data Integrity: Using a non-delegable function on a list with 10,000 records will only search the first 500–2,000, leading to silent data loss where the user doesn't realize results are missing.
• Performance: Non-delegable queries force the app to download large amounts of data, which slows down load times and increases memory usage.
• Visual Indicators: In Power Apps Studio, a blue underline or a yellow warning triangle indicates a delegation issue. 

--------------------------------------------------------------------------------------------------------------------

Pagination:

Gallery

Vertical gallery - datasource- layout change-Students List-5000 records-

PowerApps can't load in optimized fashion.

Label - CountRows(GalleryStudents.AllItems)

shows 100 only even though list has 5000 records.

Settings:

Data row limit for non-delegable quries

set it to 1

FirstN(Filter Students, Region.Value="North"),10)

It will give you 1

add hidden gallery.

This hidden gallery perform optimized loading in batches. Delegation is supported. We are not using FirstN, LastN, Pagenation things here so no issue with delegation. 

This gallery name is galStudentsMainHidden.

We are loading data from items property.

items-> 

Filter(Students, varRest && StartsWith(Title, txtStudentName.Text) && (Region.Value=drpRegion.SelectedValue|| IsBlank(drpRegion.Selected.Value))

)

In order to show visually to use, add another gallery.

Items->Here use FirstN, LastN, pagenation etc..

Pagenated Gallery screen->

OnVisible->set(varPageNumber, 1)

iconNext->Onselect->Set(varPageNumber, VarPageNumer+1);

we are increment +1

iconBack->Onselect->Set(varPageNumber, VarPageNumer-1);

we are decrementing -1

galStudentsMainHidden has total count.

In order to get the count from the hidden gallery, lblCountRows label 

Text-> CountRows(galStudentsInHidden.Allitems)

PageNation Size- dropdown field

[5,10,15,20]

if we choose 20, we need to show scrooling. 15 then no scrolling.

Main gallery to show content, Items->

galStudentsInfo->Items->

If(iconNext.DisplayMode=Disabled,

//Handling Last set of records

LastN(

FirstN(

galStudentsMainHidden.AllItems,

drpPaginationSize.SelectedVawlue*varPageNumber),

drpPaginationSize.SelectedValue - (drpPagenationSize.Selectd.Value * varPageNumber - Value(lblCountRows.Text))

),

LastN(

FirstN(

galStudentsMainHidden.AllItems,

drpPaginationSize.SelectedVawlue*varPageNumber),

drpPaginationSize.SelectedValue

)

)

StudentName is text input field for search.

Region: Dropdown based on region

-------------------------------------------------------------------

First go through delegation concept

https://tejasadventure.blogspot.com/2020/05/power-apps-understanding.html

In powerapps, we observe by default 500 item limit has set with in the app level for any data source.

In order to overcome, we have option to set the limit of 2000 item limit maximum in the app.

Now using code, MaximoFacility is my data source name contains 3000 items.

ColFacilityDropdown is the collection storing the count of items from data source. We expect to see more than 2000 items.

Based on StartWith function filtering the data with respective to the charectors and numbers as mentioned below.

Code: Place the below code in a button on select property.

Add label with code CountRows(ColFacilityDropdown)

ClearCollect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "A" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "B" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "C" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "D" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "E" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "F" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "G" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "H" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "I" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "J" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "K" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "L" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "M" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "N" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "O" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "P" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "Q" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "R" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "S" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "T" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "U" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "V" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "W" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "X" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "Y" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "Z" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "Z" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "0" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "1" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "2" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "3" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "4" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "5" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "6" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "7" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "8" )));

Better Solution using PowerAutomate flow.
https://www.youtube.com/watch?v=b250r1lET6g&t=141s

--------------------------------------------------------------------------------------