cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hantsjoel
Resolver II
Resolver II

Deleting items from Sharepoint List that are not found in Excel Table

Hi,

 

I would like to compare a Sharepoint list (around 700 items) with an Excel table and delete any items that do not appear in the Excel table. I attempted this by running a 'Get a Row' for every Sharepoint item, but the Get a Row fails when the row is not found. I know that at this point I could continue with a parallel branch that have different 'Configure Run After' settings, but I'd rather the flow didn't fail if possible.

 

My other attempt is using List All Rows Present In a Table:

 

Annotation 2020-07-31 145718.png

 

The problem with this is obviously how slow it is....around 20 seconds per item... Over 3 hours! I want to follow this up with deleting all rows in the table so I'm looking at one very long flow.

 

Is there a smarter way of doing this that I'm not seeing?

 

Thanks for your help!

 

Joel

2 ACCEPTED SOLUTIONS

Accepted Solutions

@hantsjoel 

 

Yes, you could list rows in excel than have a loop that appends the information you want to that array/string variable. (Use append)

This would increase performance drastically.

 

The reason its taking so long right now is that it literally has to list rows in excel for every item in SharePoint. 


Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





View solution in original post

I got there in the end! Here is the final flow. It works much faster than the original!

 

solution1.pngsolution2.png

 

As I'm dealing with numbers, I had some issues with everything going down the 'No' path, even when both columns were formatted as Number and I put the Excel column values into an int(). Putting the Sharepoint values into string() fixed it.

 

Thanks for all your help @Jcook, I really appreciate it!

 

Joel

View solution in original post

11 REPLIES 11
Jcook
Super User III
Super User III

Hello @hantsjoel 

 

What is the error message you get when the action fails?

 

Try changing your IF condition to:

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

 


Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





Hi @Jcook, thank you for your reply. Sorry I guess I wasn't very clear. Using 'Get a Row' fails when a row is not found. The pictured alternative above works, but it is painfully slow to run, around 20 seconds per item.

@hantsjoel 

 

Yes it would be very slow, since for each Sharepoint item you are Listing all the rows in the excel.

 

Its hard to troubleshoot without seeing your full flow, and not knowing the error you are getting?

If its just slow, try placing your excel action outside the loop, if this is not possible try changing the concurrency on the loop.

 

This can be done by clicking the 3 dots on the loop action, and clicking settings,

Than enable concurrency, and set to 50. 


Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





Thanks for the tip about concurrency, @Jcook, I'll check that out

 

In terms of putting the Excel action outside of the loop, do you think there could be any benefit to performance if I was to place all table rows into an array variable and then filtered that array variable by each SharePoint list item in the Apply To All?

@hantsjoel 

 

Yes, you could list rows in excel than have a loop that appends the information you want to that array/string variable. (Use append)

This would increase performance drastically.

 

The reason its taking so long right now is that it literally has to list rows in excel for every item in SharePoint. 


Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





View solution in original post

Thanks @Jcook .

 

Am I on the right track here? I'm not sure what expression I would use in the Filter Array to compare each Application ID in the Sharepoint list to the Application ID in the array variable :

 

Annotation 2020-08-01 220732.png

 

Annotation 2020-08-01 220836.png

to improve the performance .... and you can try configuring concurent control in "apply to each" to enable parallel opeartions performed. Go to "apply to each" -> Click on three ellipses (...) -> Settings -> Enable Concurrency and drag it to 50.

 

krishnags_0-1596323693632.png

 

Thanks @krishnags, I will do this with the new flow. 

 

I'm on attempt 3, which I think is a bit more aligned with what you were suggesting, @Jcook? Only problem is that every line is being picked up by the 'No' path in the condition. Is there a way I can limit the comparison to just the 'Application ID' columns of each the Excel table and the Sharepoint list?

 

Annotation 2020-08-03 093724.pngAnnotation 2020-08-03 093809.png

Hi @hantsjoel 

 

Try switching the values around.

 

Current item contains Array


Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





@hantsjoel 

 

Sorry, I realized you are passing the append array the full body of the excel.

 

You will want to have a second loop that loops through excel and appends each row to the array. Right now you are passing in the whole excel in the variable at one time.

 

Try:

For each loop, pass in the value from excel.

Inside loop append only the Item Name or the identifier which is going to be checked against SharePoint.

 

Now you will be able to use Contains.

 

 

If you need additional help, please send me a sample from excel and I can build a POC.


Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





I got there in the end! Here is the final flow. It works much faster than the original!

 

solution1.pngsolution2.png

 

As I'm dealing with numbers, I had some issues with everything going down the 'No' path, even when both columns were formatted as Number and I put the Excel column values into an int(). Putting the Sharepoint values into string() fixed it.

 

Thanks for all your help @Jcook, I really appreciate it!

 

Joel

View solution in original post

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (62,416)