How to load specific ID's from a table?

[mmicsa]

Hi all.

I have this situation:  in a SharePoint list, I have over 100k rows.

In order to do some calcs later on, I need the ability to load rows well over the 2000k limit. I found a way, it's based on creating a collection and loading 2k ID's at a time. Current collection code:

ClearCollect(
Collection,
Filter(
SourceTable, 'Column' = varVariable
),
Filter(
SourceTable,
_OldID >= 0 && _OldID <= 2000
),
Filter(
SourceTable,
_OldID >= 2000 && _OldID <= 4000
),
Filter(
SourceTable,
_OldID >= 4000 && _OldID <= 6000
),
Filter(
SourceTable,
_OldID >= 6000 && _OldID <= 8000
),
Filter(
SourceTable,
_OldID >= 8000 && _OldID <= 10000
),
Filter(
SourceTable,
_OldID >= 10000 && _OldID <= 12000
),
Filter(
SourceTable,
_OldID >= 12000 && _OldID <= 14000
),
Filter(
SourceTable,
_OldID >= 14000 && _OldID <= 16000
)
);

This works great, it loads all 16k rows and can be extended I guess, but I need to take load times into consideration.

Now, my problem. 

My ID's are not concurrent. What I mean by that is that. I can have a block of rows I need from ID 200 to 250. Then from 14900 to 14948. Then from 80000 to 83240 and so on. I thought of a Min(ID) and Max(ID) approach, but since they are scattered all over the dataset, MinID could be 10 and MaxID could be 90k, so no help there. My biggest set would de ~14k after applying the filter, hence why I only went up to 16k rows. 

So, ideally, I need to filter on one column, get all the ID's and then pass them as 2k blocks to my collection. 
Of course, I'll take any idea/suggestion that accomplishes the task as described, load over 2k rows for a max of ~14k rows.

Thanks all.

[WarrenBelz]

Hi @mmicsa ,

This code from a blog of mine will load all records in a list, but I think 100k will put strain on both loading time and performance.

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[mmicsa]

Hi @WarrenBelz 
I read the blog posts you indicated, and it does seem to fit my use case perfectly. 
I have "borrowed" the code (thank you), I made it work (was confused for a while until I figured out I need to add it on a Button), now I am trying to see if I can change it to take another filter. 
I have no need to read the entire 100k data set, I just need chunks out of it, the largest possible chunk being ~14K rows.
I'll tried adding this:

wDesc: Filter( Sort( MyList, ID, Descending ), Status = "Planned"

as indicated in your post but I still get the entire data set. 

Any clue why?
That would solve all my problems. 

Thank you very much.

[mmicsa]

Also from the blog, this code:

Clear(colMyCollection); With( { wDesc: Filter( Sort( MyList, ID, Descending ), Status = "Planned" ), wAsc: Filter( Sort( MyList, ID ), Status = "Planned" ) }, Collect( colMyCollection, wAsc, Filter( wDesc, !(ID in wAsc.ID) ) ) )

This works beautifully and does cover 99% of my cases, except the one where I have the 14K records. Can this one be extended to do 14-16K rows maybe ? 

[WarrenBelz · Accepted solution]

Hi @mmicsa ,

If you want all the Planned Status items, you would do this

Clear(Collection);
With(
   {
      wSets: 
      With(
         {
            wLimits: 
            With(
               {
                  wLimit: 
                  Sort(
                     SourceTable,
                     _OldID,
                     Descending
                  )
               },
               RoundDown(
                  First(wLimit)._OldID / 2000,
                  0
               ) + 1
            )
         },
         AddColumns(
            RenameColumns(
               Sequence(
                  wLimits,
                  0,
                  2000
               ),
               "Value",
               "LowID"
            ),
            "HighID",
            LowID + 2000
         )
      )
   },
   ForAll(
      wSets As MaxMin,
      Collect(
         Collection,
         Filter(
            SourceTable,
            _OldID > MaxMin.LowID && _OldID <= MaxMin.HighID && Status = "Planned"
         )
      )
   )
)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[mmicsa]

PERFECT!

It works perfectly, as expected, thank you for all the help!

Hopefully it will help someone else to load a lot of data, it's not easy.