Answered

Collection more than 2000 rows

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello there!

I am having 7000 rows inside of my SharePoint database. Since I am using PowerAutomate to automatically update my SharePoint list, I can't use the unique ID that comes with every row to make a big collection.

At first I was only having 4000 rows at maximum, but this became 7000. I was using this code earlier, which wont work anymore;

ClearCollect(col1; ProjectLijsten);;
ClearCollect(col2; FirstN(Sort(ProjectLijsten; ID;Descending);2000));;
ClearCollect(colCombined;col1;col2)

How can I make a big collection? (I am using the Distinct operator too, so I need to make a collection)

Categories:

Building Power Apps

All responses (14)

Answers (1)

Sort by

WarrenBelz 156,732 Most Valuable Professional on at

Like
a
(2)

Report
Copy link

Link copied!

@Anonymous ,

I believe I have answered something similar for you before. The only real way is as per the “big collection” process in this blog on Delegation I wrote a little while back.

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.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for your answer @WarrenBelz

I have looked at your blog multiple times, I don't think I can use any of these. Since I can't use the unique ID of the row, right?
WarrenBelz 156,732 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

@Anonymous ,

You can indirectly by creating the "shadow" ID numeric field as noted. I was just trying to point out that there is simply no other way of reliably collecting more than 4,000 rows (your example would produce duplicates and I have a more accurate/reliable process for 4,000 in my blog on using the With() statement ) other then by using this type of counter.

Delegation is a restriction that needs to be managed by planning data structure (it is not an error) and unfortunately comes more when using a non-premium data source.

One example would be if all the Distinct records were in the newest 2,000 records, or if you could filter to under 2,000 with a Delegable filter first.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Whenever we use the Distinct operator, there will be less than 2000 rows. So I guess we can do something with that @WarrenBelz

I am using the Distinct filter to get out the double rows with the same information. I have multiple rows for each project with a different number behind it.

example;

Project; Number
19037 1
19037 2
Verified answer

WarrenBelz 156,732 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

@Anonymous ,

If you have another unique numeric identifier, you can also look at using that. Also the Distinct filter will only find Distinct values from the first 2,000 records, not the first 2,000 Distinct records.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Sadly that is not an option @WarrenBelz

There are around 8000 rows in there, here is an example of why there are soo many rows in there and I am using the distinct function.

Here is how my database looks; https://gyazo.com/17dbb4432e3d65f9c33565d4f5014a33

There are rows with the same Order number, but with a different Bon nummer. I am having 2 screens;

The first screen is showing all of the unique orders, so for example, in the screenshot above, you see 16018 4 times. It will show up only once.

If you click on one of the projects, you will go to a new screen with a gallery, this will show all of the rows with that selected Order number. This all works great, except it not being able to load these 8000 rows & distinct it on the order number. I honestly don't know what to do right now.
WarrenBelz 156,732 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Anonymous ,

I am not sure this will help, but keeping in mind my comment on planning data structure, if ever I need to get a set of unique values from a big list, I maintain a separate reference list and test on every new and edited record save function if it already exists in the list and if not Patch the list with the value.

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.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for your answer @WarrenBelz

This sadly wont do the job for the project I need this for. I am having a gallery that is showing all of the projects. If they click on a project, it shows all of the details for that selected project. They can not add/edit it, only view it.

I have been searching a lot of information about what I want exactly but I do not think it is possible sadly. The only thing I could come up with is adding an extra column using Power Automate and do number++ for every row. So I am going to look into that now.
WarrenBelz 156,732 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
@Anonymous ,

Then create the list yourself the “low tech” way.

Export the list from SharePoint to Excel

Use Data > Remove Duplicates (I think that is what it is called - not on PC presently)

Import result into new reference list.

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.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hey @WarrenBelz

Just letting you know that I've done this on a different way. I've made a Power Automate flow that adds a column to the Excel list & then increments the value by 1 every row. Then on my OnStart I've done the following;
Concurrent( ClearCollect(col1; Filter(ProjectLijsten; UniekID >= 1 && UniekID <= 2000)); ClearCollect(col2; Filter(ProjectLijsten; UniekID >= 2001 && UniekID <= 4000)); ClearCollect(col3; Filter(ProjectLijsten; UniekID >= 4001 && UniekID <= 6000)); ClearCollect(col4; Filter(ProjectLijsten; UniekID >= 6001 && UniekID <= 8000)); ClearCollect(col5; Filter(ProjectLijsten; UniekID >= 8001 && UniekID <= 10000)) );; ClearCollect(colCombined; col1; col2; col3; col4; col5)

This fixed it for me! Thank you for assisting me