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?

[kscharber · Accepted solution]

You could run a multiple collections to bring in the data in chunks of 2000, then group it into a larger collection.

Powerapps just doesn't like dealing with all that data from an outside source, but temp storing within the browser  works alright.

Step 1.)

***
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=)

;ClearCollect(NameofCollection,CollectionA,CollectionB,CollectionC,CollectionD,CollectionE,CollectionF,CollectionG,CollectionH)

You should now be able to Filter your NameofCollection with all 15000 records.  It just takes a bit of preloading first to get there.

***Edited to add a Concurrent function to make this load much faster***

[Anonymous]

@kscharber  

Does this method work for you? I tried it using a SharePoint list but powerapps still only collects 2000 item, total.

Stats :

- my SP list has 4500+ items

- I collect into 3 collections using your method

- items collected per collection

   - collection1 = 1989

   - collection2 = 11

   - collection3 = 0

   - Total = 2000

To check if powerapps has a collection limit of 2000 total, I pointed collection2 at a different SP list and got these numbers:

- collection1 = 2000

- collection2 = 2000

Seems powerapps will only take the first 2000 items in a SP list THEN Filter/Sort/etc. The functions appear to need a slight tweak to make them work as intended.

Cheers

[ZePowerDiver]

Not sure, but maybe the filter is not able to delegate.. do you get the blue underline in your Filter() function?

[kscharber]

I also wonder if delegation is affecting your filter. 

I too pull from SP for all of my tables.  For me I use Indexed columns, and almost always number values to apply this method. 

It took me a while to find ways to work around delegation at first, but now that I'm further along in my program, I never let that blue underline ever affect anything anymore =P  I learned the hard way how messy my data can get.

I have 3300 records in a collection that uses this method, so I'm unsure where else the issue could be. 

Maybe someone else will have more ideas.

[Anonymous]

@ZePowerDiver  yes I do get the blue line but I thought it was OK to ignore because my data source is Sharepoint as per this https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview#delegable-data-sour...

Maybe I'm wrong? Further digging into the docs I found Choice columns and '=' operator are delegatable to SP but I get the blue line when attempting this operation

Eg. ClearCollect( colMyCol,  Filter(  mySPlist  ,  choiceCol.Value = dropdown1.Selected.Value  ) ) 

- the blue line for me appears under 'Value' part of choiceCol.Value as well as under the '=' operator. I've just been ignoring it because it's SP.

It apears from my testing that PAs creates a 2000 item instance before applying my filters, like the one above. 

ie The above code only returns those items that meet the criteria from the first 2000 items (by ID ascending order). 

My current idea is Sort Ascending/Descending and grab the top and bottom 2000 items THEN use Min/Max ID numbers from these collections to build an array of 'missing IDs' which I use ForAll & Collect and loop through the missing IDs to collect those. A very crazy work-around ...

Any other ideas would be handy though.

Cheers

[ZePowerDiver]

Shouldn't be applying your filter on the ID column as a first step?

In your example you're filtering on the ChoiceCol which is the step you should be doing on the sum of all collections, no?

[Anonymous]

The end goal is to filter on the choicecolumn, as you say, the problem I'm having is collecting the entire 4500+ items to be able to do that. Powerapps just can't do it - well not for me anyway

[mhines]

I'm a novice to PowerApps and this is one of the biggest stumbling blocks to one of the big "Quick Wins" I could get with this. Monthly one department exports a list from another database and makes it an Excel Document with about 20000 lines.

This solution appears to be what I need so that we could make it easy for someone to enter in a five-letter Symbol and to then display the six related columns.

I have tried this but I believe that I have the code in "Step 1 and Step 2" in the wrong place. I tried it on the "App Object" in the OnStart Action. That didn't work. 

I have tried it on the BrowseScreen1 in the "OnVisible", but that hasn't worked.

Maybe it's pretty clear where to insert this code - but I am  hoping someone has the quick answer so I can get a quick win on this solution.

[Anonymous]

@mhines 

Try using the code on some buttons instead. Just insert a random button somewhere in your App then add and run the code in it's OnSelect.