Answered

How can i manage to filter more than 2000 records

(2) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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?

Categories:

Building Power Apps

All responses (52)

Answers (1)

Sort by

Verified answer

kscharber 97 on at

Like
a
(15)

Report
Copy link

Link copied!

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***
Community Power Pla... Microsoft Employee on at

Like
a
(2)

Report
Copy link

Link copied!

@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 279 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

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

Like
a
(0)

Report
Copy link

Link copied!

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.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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

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 279 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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?
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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 52 on at

Like
a
(0)

Report
Copy link

Link copied!

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.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@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.
mabl4367 23 on at

Like
a
(0)

Report
Copy link

Link copied!

I have the same problem but the primary key in my case is a column with the datatype char(255). This means that the comparison operators =< and > cant be used.

Also I would like a more general solution that starts by checking the total number of records and then reads them in to a collection in chunks of lets say 500 records in each.

I tried to do that by filtering the original data source an get the next 500 records where the Id is not already in my collection but neither the "not"-function nor the "in" operator can be delegated to the sql data source so I'm out of luck.

I'm not allowed to create a view in the database, otherwise I could do that and add an index column using row_number to filter on.

Any tips?