cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlexclavijoN
Frequent Visitor

How can i manage to filter more than 2000 records

I'm working from an excel file that has 15000+ records. When i try to filter some items it won't show it all. I did some research and found that the max records that powerapps will allow is 2000. But i don't think this is the issue cause it is filtering data beyond the first 2000 records. 

Can someone help me please?

43 REPLIES 43

Hi @gitamooney ,

My source is Power BI (PowerBIIntegration.Data). I created the app from Power BI to use the filtered dataset. Maybe the problem is from Gallery1.AllItems which is coming from Power BI and maybe there is some limitation, I dont know:(

Hi 

 

my data row limit is set to 2000. my SP list have 10000 records. i use given solution as get more than 2000 records.

But this solution doesn't work, collections are get up to 2000 records only. It doesn't show more than i need to get more than 2000 records.

 

Please provide any solution or please advice any change with in the code.

 

 

 

Anonymous
Not applicable

Hi,

 

so I found the solution or let's say workaround.

 

I have an excel file with 12.000 rows with 10 users, each one of them having approx. 1200 rows/stores.

I then created 10 excel files and used if sentence to select the correct person and territory.

 

Example

I have set gallery items to 

If(Dropbox.SelectedText.Value = "Person 1", Table_Excel_Person_1, 

if(Dropbox.SelectedText.Value = "Person 2", Table_Excel_Person_2,

...

...

))

 

And then it loads less than 2000 rows and it works ok. 

It is not working for me. It says that it might not work correctly with column ID on large data sets. And hence it is not pulling all the data.

Anonymous
Not applicable

You need to create several sources (files) that are below 2000 rows. Then in IF sentence you are selecting the correct data set. 

rajivk
Regular Visitor

Thanks for ur reply mic, but I have not understood ur answer. I am looking for a solution to fetch more than 2000 (around 4k) records from a SharePoint List

Concurrent(

ClearCollect(CollectionA,Filter(NameofTable,ID<2000)),
ClearCollect(CollectionB,Filter(NameofTable,ID >=2000 And ID<4000)),
ClearCollect(CollectionC,Filter(NameofTable,ID >=4000 And ID<6000)),

ClearCollect(CollectionD,Filter(NameofTable,ID >=6000 And ID<8000)),

ClearCollect(CollectionE,Filter(NameofTable,ID >=8000 And ID<10000)),

ClearCollect(CollectionF,Filter(NameofTable,ID >=10000 And ID<12000)),

ClearCollect(CollectionG,Filter(NameofTable,ID >=12000 And ID<14000)),

ClearCollect(CollectionH,Filter(NameofTable,ID >=14000 And ID<16000))

)

 

Step 2.)

Then Collect your Collections together=)

Drrickryp
Super User
Super User

@gitamooney 

That sounds like it should be a good solution but it won't work.  ID fields are only partially delegatable in SharePoint and only with "=" .  The following operators, ">",">=","<" "<=" and "<>" cannot be delegated.  To do what you want, you need to create a "Shadow" ID field like @WarrenBelz describes in his blog here along with other valuable tips on dealing with delegation in SharePoint.   https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/ 

@rajivk ,

Please  listen to @Drrickryp here - there are several options in my blog mentioned, but you cannot do it with the ID field (you need another unique numeric identifier}

Thanks Gita. But it is not working too. Following is my code.

Concurrent(
ClearCollect(collHeadCount1,Filter('Headcount Data',ID<=12000)),
ClearCollect(collHeadCount2,Filter('Headcount Data',ID>12000 And ID<=14000)),
ClearCollect(collHeadCount3,Filter('Headcount Data',ID>14000 And ID<=16000))
);
ClearCollect(collHeadCount,collHeadCount1,collHeadCount2,collHeadCount3);

It says "Delegation warning: Filter part of this  formula might not work correctly on large datasets"

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,366)