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

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

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:

NameValue
a2
b3
a4
b5
a6
b7
a8
b9
a10
 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

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

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:Test1.gif

 

 

 

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Rick72
Impactful Individual
Impactful Individual

Hi @Anonymous,

 

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

 

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

 

Robot Happy Rick

v-xida-msft
Community Support
Community Support

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:Test1.gif

 

 

 

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (43,477)