So, one thing I’ve wanted to do on this blog is to be able to get some Quick tips out to readers who are interested in small things I might have run across while implementing PowerApps.  So, without further ado, welcome to the PowerApps Tip Corner!  No, it’s not an awesome name, and I’ll probably rename it in the future, but for now….

You may need to query a data source that has more than 500 rows..  It can happen 🙂

So, an minor annoying thing with PowerApps right now is the inability to query past the first 500 records of a data source.  For this particular tip, I will be focusing on SharePoint, mostly because I ran into this exact situation.  Here was my problem:

I needed the ability to filter certain records by a Choice column that allowed multiple values (PowerApps does not currently support Multiple Choice columns).  So, I migrated the data in those columns into a multiple line column.  Great!  This means I could now filter these by using the ‘in’ operator:  For example:

Filter(‘MySPList’, “FilterValue” in Multilinestringfield)

But, this brought another issue:  The 500 item limit.  What happens is, PowerApps will only query the first 500 items in the SharePoint list, and will not bring back any results past 500.  (Note:  my SP List had 750 items).

Ugh…  So, Microsoft has been working on getting around this, by allowing you to delegate certain operands back to SharePoint, so it can do the processing.  This will allow you to get past the 500 item limit!

But….  The only operands that currently supported for this are the ‘=’ and the ‘StartsWith’ operands.  Oh, and the columns in SharePoint have to be indexed for these to work.  (Guess what type of field you can’t index?  That’s right, A multi-line field 🙂 )  So, I’m still out of luck.

It’s not the best solution, but it works..

So, my workaround for this was to leverage the ‘StartsWith’ operand for a different column (Indexed Single line of text), which I could filter to pull in 463 results, and then toss those into a collection.  Then I did it again with the rest 🙂

ClearCollect(mycollection, Filter( ‘MySpList’, StartsWith(SingleLineofText, “Filter1” )|| StartsWith(SingleLineofText, “Filter2”)|| StartsWith(SingleLineofText, “Filter 3”)))

This started my Collection, and collected 463 items into my PowerApp.  I then followed it up with this command:

Collect(mycollection,, Filter( ‘MySpList’, StartsWith(SingleLineofText, “Filter4” )|| StartsWith(SingleLineofText, “Filter5”)))

This appended my collection with the rest of the items, and now I can perform any query against this collection in the comfort of my PowerApp!

The Drawback

Obviously, this isn’t something you want to do for a list or table with hundreds of thousands, or millions of rows.  This is a stopgap to get you over the 500 limit hump.  But, you should be prepared for a delay while it pulls in your data.  In my case, on a SharePoint list with 750 rows and 70 columns, it adds about 7-8 seconds of load time to the app.  You can certainly run this on your ‘OnVisible’ or ‘OnStart’ property of the home screen to help mitigate this delay to your users.

I’m hoping this limitation is something PowerApps will get past in the near future, but for now, here’s a stop gap measure if you just need a bit more wiggle room on your data querying.

Please check out some of my other articles to take a deeper dive into the product, and as always, please feel free to reach out to me at jo.karnes@centricconsulting.com