Filter on Excel Datasource (NonStatic) that has >500 records

[Anonymous]

Hi all,

I am trying to do a simple filter on a excel sheet that has ~3000 records of data and is being accessed from a OneDrive Connection. Each record has a name, date, and value. What I have been trying to do is display to the user all items with their name which is always less than 250 results. I am trying to do things the correct powerapps way and to use the filter() to only return those 250 results.

For example: ClearCollect(UserData,Filter(DataSource, Name="CurrentUserName"))

*UserData is empty for any user that has records past the 500 mark in DataSource

The problem is that the filter doesn't look through any rows after 500, so the filter only works for the first 2 users. To my understanding, Powerapps has a 500 record limit on returns. I have seen plenty of post about people trying to Display more than 500 results, but that is not what I am doing.

Ideally, I want to filter even more by date and name, but even a simple filter is not working since I know some functions can't be used in filtering.

Additional test:

Name	Value
a	2
b	3
a	4
b	5
a	6
b	7
a	8
b	9
a	10

 Here is a sample of a test table, the table repeats all the way up to 700.

 For example, with this test data source, how would I create a collection that only holds records where value>400?

Currently, if I use: 

Filter(DataSource, value>400)

I only get 100 results, when I should be getting 300 results. This is not breaching the 500 record retrieval rule correct?

Thanks

[Rick72]

Hi @Anonymous,

The issue here is that that the data is in Excel. Excel does not support delegation:

• https://powerusers.microsoft.com/t5/General-Discussion/Delegation-for-Excel/td-p/50268
• https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list

When you transfer the data to a SharePoint list, it will work.

Rick

[v-xida-msft · Accepted solution]

Hi @Anonymous,

I think you have a misunderstanding in the Non-delegable limits in PowerApps.

The Excel datasource (Excel table) is not a delegable data source within PowerApps, so you have faced a Non-delegable issue with the formula (that you provided) within your app.

If you have faced a Non-delegable issue with the formula within your app, you could only process 500 records locally at most in default. In other words, you could only process first 500 records of your test data source (test table,which has 700 records) locally in default, the remaining 200 records would be ignored by your filter function.

So the Filter(DataSource, Value>400) formula that you provided could only filter first 500 records of your test data source (test table), and find the records whose Value is more than 400 (On your side, only 100 records whose Value is more than 400 within the first 500 records of your test data source).

If you want to filter more records from your test data source, please take a try to change the Non-delegable limits (Data row limit for non-delegable queries) within your app, the maximum value of the Data row limit for non-delegable queries is 2000 currently.

More details about changing the Non-delegable limits within PowerApps, please check the following article:

Change the Non-delegable lmits

The GIF image as below:

Best regards,

Kris