Using Powerautomate to update sharepoint list

[raxer0071]

Hi everyone,

I am trying to update a sharepoint list using data from excel (that is uploaded to sharepoint of course). I think the current problem is that the list is so big that the flow cannot even run properly (>2285 rows + 11 columns). All the data is historical data that should not be changed. So new data is appended everytime. All i need to do is for powerautomate to append the new data that is added to the exisitng sharepoint list. How do I do that?

Here's what my current flow looks like:

 

 

 

[Nived_Nambiar]

Hi @raxer0071 

The mistake i could see here is in the Get items action, you have to use filter query that can filter the list based on excel data, Assume there is a column named RecordID which would be identifier for both list item and excel file which can be used to identify the record to be updated.

So try to filter the list item record based on a property which makes both record and row as unique and then use update item accordingly. This makes things faster 

see below how odata filter query can be utilized in get items

How To Use OData Filters In Power Automate With SharePoint

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[creativeopinion]

@raxer0071 If you want your flow to run more efficiently, you'll need to use a Filter Array action to check and see which items already exist in SharePoint. This way you only need to loop through the items you need to update. Also, with a Filter Array action you can check to see which items do not currently exist—and create a new item if necessary. 

The way you currently have your flow set up means that you need to loop through every single row of your Excel table and run a check on each item—this is inefficient. With a Filter Array action you can filter the items that:

• Already Exist — then update
• Don't Exist — then create
• Exist in SP and Not in Excel — delete (if this is something you want to do)

Reference the first section of my YT tutorial: Are you using the Microsoft Power Automate Filter Array Action wrong?

Although, I only cover how to Create and Delete items in this tutorial—you can use the same concept to include actions to update an item in SP if it exists in your Excel table.

In this video tutorial I’ll show you 3 practical ways to use the Filter Array action and how to use it properly.

1️⃣ Cross-Referencing Data

2️⃣ Filtering by Key

3️⃣ Substring Matching

Did you know that the Condition action has a limit of 10 conditions? Although it might look like the Filter Array action can only accept one condition—this is not true. By using the advanced mode you can enter multiple conditions into a Filter Array action with an expression.

IN THIS VIDEO:

✅ 3 Ways to Use the Filter Array Action

✅ How to use the Scope Action to Group Actions

✅ How to Check the Number of Items returned from a Filter Array Action

✅ How to Cross-Reference Data in Excel with a SharePoint List

✅ How the Filter Array Action Works

✅ How to Access the Dynamic Content from a Filter Array Action

✅ How to Filter Items by a Key

✅ How to Filter Items by Matching a Substring

✅ How to Use Multiple Conditions in a Filter Array Action

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram

[raxer0071]

Hello @Nived_Nambiar ,

I have tried to cut down the number of data to be appended using Filter Array. From 2.27k items now there is only 7 new items to be appended. However it still only works up till apply to each. Could you please provide some more guidance?

Thank you!

[Nived_Nambiar]

Hi @raxer0071 

seems like flow is take more time there (still executing)

Also could you confirm that there are more data in excel as well (as i earlier thought there are data in sharepoint and less data in excel)

Also note that apply to each has a limit too on numbers of items to be looped.

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[raxer0071]

yeah I had already ran it two times. First time it ran for 30mins even though there is only 7 items to be appended after filter array. The entire excel sheet has about 2.2k items of historical data.

[Nived_Nambiar]

But if i am right> you need to add new data from excel to list right?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[raxer0071]

yep thats right! 

[Nived_Nambiar]

So is there any column by which we can distinguish old records in excel and new records by which we can only take new records ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[raxer0071]

Hi @Nived_Nambiar 

Yup there is!

Thers is a column called document_no. which has unique alphanumerical number for each document.

[Nived_Nambiar]

How it can be able to distinguish the new records ? i think may be by lookup in sharepoint list right ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[raxer0071]

In the Excel data, there is a column called "Batch" and this column basically records the date of the last upload. So I have set up a filter query to only return data that is after a certain date.

e.g. Latest batch data is 31/10/2023, new batch data is 15/3/2024. So my filter query reads "Batch" is greater than "45230". 45230 is the the new batch data date (15/3/2023) in ISO format.

The data filter correctly, but somehow it cannot converge once it reaches the next step which is "Apply to each"