How can i manage to filter more than 2000 records

[Anonymous]

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?

[bulprosbibg]

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:(

[upputuriyamini2]

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]

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. 

[rajivk]

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]

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

[rajivk]

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

[gitamooney]

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]

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

[WarrenBelz]

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

[rajivk]

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"