I have a document library with the document set content type and document content type in it. In the document library there is more than than 8 Lakhs (0.8 million) document. In the "My Document" content type there is a "DocumentStatus" choice column which has value Draft, In Progress, Approved, no default value is set in it - so, there are thousands of documents which has no "DocumentStatus" value.

I want to find all documents where "DocumentStatus" is empty from this big document library. I tried the Do Until loop approach to get more than 5000 items using Power Automate but it is executing for 2 cycles which is giving 5000*2 =10000 items after that the flow is getting successfully completed. But I want to query all 0.8 million documents, performance doesn't matter for me as it is one time activity.

Was anyone able to retrieve about a million documents using Power Automate successfully?

I am looking for such Power Automate and PowerShell reference. If anyone experienced querying big number of items from SharePoint, requesting to share your knowledge here.

2 Answers 2


When you do something heavy, don't use Power Automate. It's not meant for huge queries, but for bite-size automation.

Use Get-PnPListItem from PnP.PowerShell, with pagesize of 1000 (1000 items per batch).

Get-PnPListItem -List Tasks -PageSize 1000 -ScriptBlock { Param($items) $items.Context.ExecuteQuery() } | ForEach-Object {
    #do something here, the item is in $_ object. Assuming your DocumentStatus is a choice field.
    if ($_.FieldValues.DocumentStatus -eq "Draft") {

Reference: https://pnp.github.io/powershell/cmdlets/Get-PnPListItem.html#example-8


According to this Microsoft article, there is a hard limit of 100 000 items. Pagination is not turned on by default. You would need to turn on Pagination and increase the threshold to 100 000.

Name Limit Notes
Paginated items 5,000 for Low, 100,000 for all others To process more items, trigger multiple flow runs over your data.
  • That limit is using the pagination technique - but I am trying using the Do Until loop where pagination is applicable, each cycle of the loop it is supposed to retrieve 5000 items, and this will continue till the end of the items in document library. This is the explanation of the Do Until loop, but in my case after executing for 2 cycle (retrieving 10K Items), the flow is getting completed successfully. Actually this is not expected, this should continue to execute till the last item in the list/document library.
    – SP 2022
    Dec 21, 2022 at 4:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.