cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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?

51 REPLIES 51

I saw that after I replied ^^

@Anonymous 

I know Sequence() is the best for future maintenance, but I tried with a list that have 13,5k items (yes I had that solution implemented before) and the difference of loading data was from 25 seconds Sequence() to 5 seconds with the Concurrent(). Of course if the list grows like 100 items per day, the Sequence() gives you better control because you don't need to change/add more collections. Both options have ups and downs, we just have to think what is the best for us, our app and the user 😊 sorry for the double post with same solution, but never thought about the batch option and was so excited to share 😅 cheers

Anonymous
Not applicable

@User7 

That's interesting feedback re the timings. I wondered how Sequence would fare. I don't have a list large enough to test it on so didn't know if it would be slow or not.

 

I might have to see if I can work some Concurrent magic into that method 🙂

mdevaney
Super User
Super User

@Anonymous 
I've written an article showing 4 different ways to Create Power Apps Collections Over 2,000 rows.  You can check it out below.  

Link to Article:

https://matthewdevaney.com/create-power-apps-collections-over-2000-rows-with-these-4-tricks/

 

powerapps-delegationover4000-featured.jpg

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Hi kscharber, 

 

thanks for sharing that great idea. 

But in my case it still limits the final collection to 2000 - were there some changes made by Microsoft in the meantime, or am I doing something wrong?
Please find my code below. Thanks for a short feedback in advance!

 

BR, Rubi

Concurrent(
ClearCollect(colBusPartnersA,ShowColumns(RenameColumns(Filter(LU_BusinessPartner,ID<2000),"ID","Id","Title","Value"),"Id","Value")),
ClearCollect(colBusPartnersB,ShowColumns(RenameColumns(Filter(LU_BusinessPartner,ID >=2000 And ID<4000),"ID","Id","Title","Value"),"Id","Value")),
ClearCollect(colBusPartnersC,ShowColumns(RenameColumns(Filter(LU_BusinessPartner,ID >=4000 And ID<6000),"ID","Id","Title","Value"),"Id","Value")),
ClearCollect(colBusPartnersD,ShowColumns(RenameColumns(Filter(LU_BusinessPartner,ID >=6000 And ID<8000),"ID","Id","Title","Value"),"Id","Value")),
ClearCollect(colBusPartnersE,ShowColumns(RenameColumns(Filter(LU_BusinessPartner,ID >=8000 And ID<10000),"ID","Id","Title","Value"),"Id","Value"))
);
ClearCollect(colBusPartners,colBusPartnersA,colBusPartnersB,colBusPartnersC,colBusPartnersD,colBusPartnersE);

@brou1973 ,

You cannot use ID for the filter as it is not Delegable other than = (equals). You need another unique numeric sequential identifier field.

Thank you, Warren! 
In the meantime I got to manage it by implementing a "GroupingID" field, that I set based on the ID with a small workflow on item created. 
BR, Rubi

Anonymous
Not applicable

Hi @kscharber , If i do this, then I get blank values. I am working with an SQL server. Explicit Column is turned off.

This approach will not work anymore as the ID filter is not delegable, 
instead try using a different field that you know you can filter by and each filter option will not go over 2,000 items 

What worked for me was filtering by Created date 
the item creation date started after 2015 and each year they created about 5000 items,  currently they have 3,000
I prepared the script so it can take created items until 2029

in your case if they created more than 2000 each year, then you should make the date range smaller 

Concurrent(
,ClearCollect(CollByYear1,Filter(,Created>Date(2015,12,31)&&Created<=Date(2016,12,31) ))
,ClearCollect(CollByYear2,Filter(ListTitle,Created>Date(2016,12,31)&&Created<=Date(2017,12,31) ))
,ClearCollect(CollByYear3,Filter(ListTitle,Created>Date(2017,12,31)&&Created<=Date(2018,12,31) ))
,ClearCollect(CollByYear4,Filter(ListTitle,Created>Date(2018,12,31)&&Created<=Date(2019,12,31) ))
,ClearCollect(CollByYear5,Filter(ListTitle,Created>Date(2019,12,31)&&Created<=Date(2020,12,31) ))
,ClearCollect(CollByYear6,Filter(ListTitle,Created>Date(2020,12,31)&&Created<=Date(2021,12,31) ))
,ClearCollect(CollByYear7,Filter(ListTitle,Created>Date(2021,12,31)&&Created<=Date(2022,12,31) ))
,ClearCollect(CollByYear8,Filter(ListTitle,Created>Date(2022,12,31)&&Created<=Date(2023,12,31) ))
,ClearCollect(CollByYear9,Filter(ListTitle,Created>Date(2023,12,31)&&Created<=Date(2024,12,31) ))
,ClearCollect(CollByYear10,Filter(ListTitle,Created>Date(2024,12,31)&&Created<=Date(2025,12,31) ))
,ClearCollect(CollByYear11,Filter(ListTitle,Created>Date(2025,12,31)&&Created<=Date(2026,12,31) ))
,ClearCollect(CollByYear12,Filter(ListTitle,Created>Date(2026,12,31)&&Created<=Date(2027,12,31) ))
,ClearCollect(CollByYear13,Filter(ListTitle,Created>Date(2027,12,31)&&Created<=Date(2028,12,31) ))
,ClearCollect(CollByYear14,Filter(ListTitle,Created>Date(2028,12,31)&&Created<=Date(2029,12,31) ))
);

Drrickryp
Super User
Super User

@Mpalomares 

Given the size of your data and the requirement to keep it up to date, I suggest switching from SharePoint to Dataverse or SQL.  

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,680)