Delegation limitation using Excel

[gkakun]

Hi all,

Im using Sharepoint for my app, but Im also using Excel table as a data source, with more than 2K rows. 

Im trying to bring 1 column from the excel to drop down/combo box, and using filter function to override the 2K limitation but the filter is not working properly. 

in the combobox the formula is: 

Sort(Filter(colEvents,StartsWith('Activity Uniqe ID',txtModule.Value)),'Activity Uniqe ID',SortOrder.Ascending)

(txtModule.Value is the text box Im using to type the event i want) 

colEvents collect the data from the excel- 

ClearCollect(colEvents,Table1.'Activity Uniqe ID');

Activity Uniqe ID is the column I need from the excel file 

Any idea why with filter function Im still getting only the first 2K rows? 

thanks in advanced! 

[RogierE]

Strange, because the ClearCollect is unfiltered, you should get all records. Have you checked the number of records in colEvents? You can do this by adding a label temporarily and using the CountRows(colEvents) formula

Have you tried using Search in stead of Filter? (Search is better deligable then StartsWith, but you would need to translate the column and value to text)

Please like the reply and/or accept it as the solution if the answer is what you were looking for.

[gkakun]

Thanks. I checked the number of row in the collection- 2000. Cant understand why I see only 2000 when Im filtering the collection. 

Will try with search, I never used this function 

[RogierE]

If the unfiltered collection is only 2000 records, search will not improve that. I guess this is a restriction of Excel as a data source then. Have you considered moving your data to a SharePoint list? 

[gkakun]

Hi,

You mentioned the ClearCollect, I suspect I may put it in the wrong place. the formula

ClearCollect(colEvents,Table1.'Activity Uniqe ID')     is located in the app "Onstart" - is it the right place? maybe this is the problem? 

[RogierE · Accepted solution]

It’s okay to have the clearcollect in your OnStart. Only thing to be aware of is that while editing your app, the onstart will not run. If you change it, you have to right-click your App in the tree view and select “run onstart”. 
This is not the reason for your problem though. As you can read here , https://powerusers.microsoft.com/t5/Building-Power-Apps/PowerApps-can-t-get-more-than-2000-rows-from... the Excel connector is limited to 2000 records. The workaround suggested is to use a PowerAutomate flow, but I guess this will slow down your App on startup. 
I’d still propose you create a SharePoint list by importing your Excel, and then work with a list as a DataSource. I believe the limit for SharePoint list is around 2 million records, so better luck that way. 😉

Please like the reply and/or accept it as the solution if the answer is what you were looking for.

[WarrenBelz]

Hi @gkakun ,

Just expanding on the good advice given by @RogierE , Collect/ClearCollect is a client-side (done by the local device) operation with the output subject to your (2,000) Data Row Limit, so you can never collect more than 2,000 rows in one "collect" action. There are ways to "stack" collect functions into one larger collection, but Excel is a terrible data source where things that possibly should work simply do not.
You could try this, which technically should work (I am trying to give Excel as little work as possible) - I assume that 'Activity Uniqe ID' is a Text field as StartsWith() does not work on numbers.

With(
   {
      _Data:
      Filter(
         Table1,
         StartsWith(
            'Activity Uniqe ID',
            txtModule.Value
         )
      )
   },
   Sort(
      _Data,
      'Activity Uniqe ID',
      SortOrder.Ascending
   )
)

A far better idea is to use SharePoint, where your initial code can query the List directly in a Delegable manner with no limitations on the data row size. 

[gkakun]

got it, thanks for your help! 

[gkakun]

by the way, I had the data in a sharepoint list, the problem is that the data source is changing on a weekly basis and I had a lot of issues with the app after I updated the sharepointlist with the new updated data. I thought its simpler to just keep it as an Excel and just override with the new data, but looks like its had own issues. 

Thanks!

[Johnnywhite]

I was having the same problem with filtering data from Excel tables in Sharepoint. It seems like you're facing the 2K row limitation even with the filter function. To address this, you can try using Excel's built-in functions directly within Sharepoint.

For your specific case, if you're looking to calculate percentage within Excel, you can utilize the Calculate Percentage function. In Excel, you can achieve this by dividing the desired value by the total and then multiplying by 100. For example, if you have values in cells A1 and A2 and want to find the percentage, the formula would be "=A1/A2*100".

So, to implement this within your combo box formula, you could adjust it to something like:

Sort(Filter(colEvents,StartsWith('Activity Unique ID',txtModule.Value)),'Activity Unique ID',SortOrder.Ascending)

This should help you bypass the 2K row limitation and accurately filter your data from Excel to Sharepoint. Let me know if you need further assistance!