Need to count & sum item from Large SharePoint list (10,000-15,000 items) in canvas app

[DublinOH_User]

Hello,
I am grateful for any help or ideas anyone can offer.

I have a Sharepoint list as a source for a canvas app that will vary between 10,000 to15,000 lines each month.
Challenge A -  is how can I break the data into filtered (collections, groups, other) so filtered results can be counted, and then with those results add and divide as needed. 

Challenge B - how to create a Distinct list from one column [F] since the list has a >2000 data set. The [Distinct] would result in a list under 200 choices and I am trying to avoid building a second lookup list or a manually built collection - if possible.

Roadblocks I have encountered:

• There are two columns that will filter the data into results less than 2000 rows, but delegation does not allow the collection to filter through the whole SharePoint list. (only returning the first 2000)
• Even if it was possible to create collections under 2000, one collection would still have to be filtered a second time before those results would be under 2000. (collect based on D filtered by P)

I have found all the formulas I need for adding collections and filtered groups - because I can verify that my results match the data from the first 2000.

I saw the articles on trying to make collections with ID numbers - but wasn't able to successfully create a 'number' ID column that canvas would treat as an integer. They used the ID number to collect rows 1<1999, rows 2000<2999...and so on.

• HOW TO SOLVE DELEGATION LIMIT ISSUE IN POWER APP?-FOR 2000 RECORDS
• Overcome 2000 Items Limit Using Power Apps Collect Function 

For the sake of communicating let's just say I have a table with the following:
Column F  - choice data of which a distinct should return about 200 names

Column D - choice data of which there are 7 distinct, and if used as a filter - only one would be greater than 2000 ( so it would have to be sub-filtered into two smaller collections - meaning use 2 criteria

Column P - choice data of which there are 5 distinct, half of which return more than 2000

Example of formulas we are trying to accomplish that need to include all 15,000:

Filtered count of [P] for each [F]/ Count total number of all [P] for each [F]

Filtered count of [P] for all [F]/ Count total number of all [P] for all [F]

Thank you for your valued time,
Dublin Ohio User

[WarrenBelz]

Hi @DublinOH_User ,

Unfortunately not unless you create a new list - SharePoint just continues on. What you have to realise is that the deleted items are in the recycle bin (for 93 days anyway) and can be reinstated, so you cannot have duplicate IDs.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[DublinOH_User]

Would you know if we could write a FLOW that currently gets data from Excel table and creates the SharePoint list items to start the ID Filter column count at 1 each run instead of using the list ID as dynamic reference?
Currently, we wrote the IDFilter FLOW is separate from the Upload from excel FLOW, but what if we combined them????
What actions would start the IDFilter column at 1? 
How much longer would adding to the upload FLOW take to upload 10K-15K lines, it already takes about 90 min-120 min?
(We do not have access to Premium license)

[DublinOH_User]

2 Questions:
Since all list items will be deleted each month - and new lines uploaded from Excel - would adding a column formatted to number value - named IDFilter in the spreadsheet work for creating the Collect solution mentioned above?
or 
Would adding to the upload FLOW actions to set 'IDFilter' SP column to start with 1, with another action to increment by 1 a better way to handle it? (I am not sure how to create these actions  - so providing the steps would be greatly appreciated)

Thank you,
DublinOH_User

[WarrenBelz]

@DublinOH_User ,

I am not sure why the issue of the ID (which is unseen) is a problem, but your workarounds are a way of doing it. The numeric column in Excel would do the job - it would be better to increment the number in Excel and import it rather than doing it in the Flow. I am not familiar with the timing of Excel rows (other than I know they take a while) - the other thing you need to be aware of is the potential Flow cycles in your Licence limit.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[WarrenBelz]

Hi @DublinOH_User ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[DublinOH_User]

The solution that worked best was to include the integer column in the excel source file. For our needs, which is deleting and uploading all 10-15K rows every month - providing the number in the data set was the faster solution. 
We then used the suggestion above to filter collections based on the ID_Integer column. I am not done with building all the collections and COUNT formulas so I will post the "accept as solution" after all the developing and testing is complete.

I used Power automate to delete and upload data from excel.  We created a separate Power app with buttons that call to the Parent-Child solutions to replace the data sets for each list.

[DublinOH_User]

 STILL TRYING TO COUNT/SUM & DIVIDE Large Data set

Thank you for working this through with me. There is progress but not success. @WarrenBelz 

Successes so far

✓ The flows to delete each list and re-upload are working

✓ The list will filter and collect based on the 'ID_Filter' integer column that was created in excel

✓ The below collection methods of all 10-15K items works - but is CRAZY slow, like 5-7min to create collection

HOW TO SOLVE DELEGATION LIMIT ISSUE IN POWER APP?-FOR 2000 RECORDS

Overcome 2000 Items Limit Using Power Apps Collect Function 

Remaining Challenges

• Could you help me with how I can get a Distinct list from one column - but all 10-15K rows?
• Making sure all the items in the list are accounted for in the math formulas

As for counting and performing all the actions needed,  I guess I will have to add all the smaller collections together each time we need a mathematical result. Unless you have any other ideas.

Does a model-driven app work better with these larger data sets?

[DublinOH_User]

We need to filter through all 10-15K items - it is only currently returning results for the first 2000
Sort(
GroupBy(
Filter(
'PF_PM_ Compliance_OverNext30',
'PM STATUS'.Value = "ConditionA"Or'PM STATUS'.Value = "ConditionB",
'SERVICE COMPLETED DATE' = Blank()
),
"PLANT_x0020_CODE",
"PlantGroup"
),
"PLANT_x0020_CODE",
Ascending
)

[WarrenBelz]

Hi @DublinOH_User ,

I do not use Model-driven apps as there is nothing in a Model Driven app that cannot be done in a Canvas App, but a lot cannot be done in the reverse - also the limitation is SharePoint Delegation, not Power Apps.

The posts you refer to are similar to my blog (but I have a few more options),

Have you tried simply applying the Distinct formula to the collection? I am not sure how you would accomplish the second question.

[WarrenBelz]

@DublinOH_User ,

Correct - the GroupBy result is subject to your Delegation limit (it is a "hidden" Delegation limitation)