Best way to update SharePoint list with more than 5000 items using Power Automate

In this “Update SharePoint list with more than 5000 items using Power Automate” article, we will learn how to update a SharePoint list that has more than 5000 items using Power Automate.

If you have a large list that has more than 5000 items and later you add a new column to the list or you want to update some column values, we can use this Power Flow to update the list.

Update SharePoint list with more than 5000 items using Power Automate

Here is our Global-Superstore list, which has 7100 items.

For this demo, we have added a new column named “Updated” with a choice type (Yes and No values in it).

Here, what we will do is update the “Updated” column with “Yes” wherever it has “No.”

The Power Automate flow shown below will update the SharePoint online list flawlessly, even if it contains more than 5000 items.

For this demo, we have created a manually triggered flow, but you can also convert this to a scheduler flow.

In this section, we will explain each action in the above flow.

Flow Explanation: Update SharePoint list with more than 5000 items using Power Automate

Below are the explanation step by step:

Variable initialization:

Initialize the below variables:

• arrItemsFromSPList – Type Array
• intLastItemIDfromSPList – Type Integer, default value 0
• boolVarIsArrayEmpty – Type Boolean, default value false

Variable initialization – update more than 5000 items in Power Automate from the SharePoint list.

Do until loop in Power Automate to update more than 5000 items

Add Do Until loop control. And add the below:

• boolVarIsArrayEmpty is equal to true
• Change limit counts to 5000 from 60 (default).

Then, add the Get items action and followed by other actions shown below.

Get Items action inside Do Until loop in Power Automate

Add a Get Items action inside the loop and pass the below parameter to it:

• Site Address: Your site’s URL.
• List Name: Your list’s name (Title).
• Include Nested Items: Select “No” when you are getting data from a list or “Yes” when you are reading data from a document library. In general, for lists and document libraries, we need to pass this as “no” or “yes.”
• Filter Query: ID gt intLastItemIDfromSPList and Updated eq ‘No’. This filter will return items whose “Updated” value is No.
• Top Count: Enter any number which is less than or equal to 5000.

Total item count length from the Get Items action query in Power Automate

Add a compose action to check the total item count returned by the Get Items query.

length(body('Get_items')?['value'])

Apply to each loop to update items in SharePoint Online using Power Automate

Add an apply to each loop control and add the below in “Select an output from the previous step.”

outputs('Get_items')?['body/value']

Update item action inside the apply to each loop in Power Automate

Add a update item action inside the loop and pass the below parameter to it:

• Site Address: Your site’s URL.
• List Name: Your list’s name (Title).
• ID: Select from the Get Items output.
• Updated Value: Select as Yes (“The “Updated” is a choice column in the SharePoint list which has two values Yes and No).
• Rest all columns are left blank because we do not intend to update them.

For the ID column parameter, you can pass the below value directly as well:

items('Apply_to_each')?['ID']

Select data operation in Power Automate (Power Automate map values)

This step is optional, but it is recommended.

Add a Select action to filter columns from the Get Items query.

Add the following to the From field, or choose directly from the Dynamic Content tab.

outputs('Get_items')?['body/value']

Technically, the Get Items query will return all columns that you may not require, slowing down query execution. So, in the select data operation, map the columns you need, which will double your query performance, and you could also control whether sensitive data is exposed.

Add compose action for union (union function in Power Automate)

Add a compose action for the union function. Add the below for the compose action inputs:

union(variables('arrItemsFromSPList'),body('Select'))

Add a set variable action to assign the list items value to the array variable

Add a set variable action to assign the list item’s value to the array; it is the output of the previous compose action.

outputs('Compose')

Add a condition control to check Get Items query result is empty

Add a conditional control to check to Get Items if the query result is empty. To check this, perform the below steps:

empty(body('Get_items')?['value']) is equal to true

If the above condition returns true, set the variable value for “boolVarIsArrayEmpty” to true, and on the false side, set the variable value for “intLastItemIDfromSPList” as below:

last(body('Get_items')?['value']).ID

Update SharePoint list with more than 5000 items using Power Automate – Demo

Now, manually trigger the above flow. Wait for Sometimes, the waiting duration depends on the number of items on your list; in our demo, for about 7000 items, it took 00:58:32.

After successfully completing the flow execution, we could see that the “Updated” column had been updated with the value “Yes.”

Summary: What we had here (Update SharePoint list with more than 5000 items using Power Automate)?

Thus, in this article, we have learned the following with respect to updating more than 5000 items in SharePoint Online list using the Power Automate

• How to retrieve more than 5000 items in Power Automate from a SharePoint Online list
• How to use the Do Until loop in Power Automate
• How to use apply to each loop in Power Automate to update item in SharePoint Online list
• How to use the filter query in the Get Items action in Power Automate
• How to use the compose data operation action in Power Automate
• How to use the Select data operation action in Power Automate to map the SharePoint columns
• How to use the array variable in Power Automate
• How to use the Union() function in Power Automate

See Also: Power Automate Tutorial

You may also like the below Power Automate tutorials:

• Best way to get more than 5000 items in Power Automate from SharePoint Online list
• 2 ways in SharePoint Online check in all files using Power Automate quickly
• Quickly add multiple items in SharePoint list using power automate – more than 5000
• Instantly Delete items from two list using Power Automate: Delete SharePoint list item based on another list
• 1 Best way to clone SharePoint list: Synchronize two SharePoint lists using Power Automate
• Promptly generate unique ID in SharePoint list using Power Automate – Microsoft 365
• Best way update list item in SharePoint using Power Automate – Microsoft 365
• 3 ways instantly disable Action in Power Automate
• Instantly in 2 steps Copy and Paste action in Power Automate
• Discover exception handling in power automate using Try Catch Finally Scope – in 3 steps instantly
• Instantly Solved: Error from token exchange: Bad Key authorization token in Power Automate – Microsoft 365
• Scheduler Power Automate: Move Old documents in SharePoint Automatically using Scheduled Cloud Flow
• Conditional trigger power automate: Trigger Power Automate (Flow) based on condition in SharePoint Online – Verified
• Power Automate Send Email: Difference between “Send an email notification (V3)” and “Send an email (V2)”
• HTML Table in Power Automate: Learn HTML table formatting using Microsoft Power Automate step by step
• In just 2 marvelous steps export import power automate flow quickly
• Get started with Power Automate

About Post Author

SP Maven

As a SharePoint and Power Platform Maven, my greatest joy comes from sharing my expertise with colleagues, friends, and the tech community, helping them navigate the ever-evolving world of technology and guiding them through the dynamic landscape of modern technology.

See author's posts