web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id : LLnooeYXt1Ds4KNQvxNqtK
Power Automate - Building Flows
Answered

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

Like (0) ShareShare
ReportReport
Posted on 31 Jul 2020 14:01:54 by

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

  • ComicSansrival Profile Picture
    151 on 16 May 2025 at 03:47:49
    Deleting items from Sharepoint List that are not found in Excel Table
    Hi,
     
    Can anyone share a procedure with image? I am trying to replicate this but unable to get expected result.
  • Community Power Platform Member Profile Picture
    on 29 Apr 2022 at 15:30:39
    Re: Deleting items from Sharepoint List that are not found in Excel Table

    Hi @Teresa24486, yes I did, and I have improved it over time. Here is what I do:


    1. Use Get Items and List Rows Present in a Table actions to load both the SharePoint list and the Excel table.

    2. Use a Select action to select a key column with unique values from the List Rows Present in a Table. E.g. I have a column called "Application ID" with a unique ID for each row.

    3. Now we have all the unique values in the Excel table, we will find out which don't appear in the corresponding column in the SharePoint list. Using a Filter Array action, filter the body/value of the Get Items action where the output of the Select action does not contain the value in the key column in the SharePoint list. The output of this Filter Array will be the unique values that do not appear in the excel table.

    4. Use an apply to each to cycle through these values and delete the associated item in SharePoint.

     

    It's a little hard to explain, but I hope that helps!

  • Teresa24486 Profile Picture
    611 on 11 Apr 2022 at 08:11:29
    Re: Deleting items from Sharepoint List that are not found in Excel Table

    Hi,

    Were you able to complete the Delete items from the SharePoint list which are not present in the excel sheet?

     

  • Verified answer
    Community Power Platform Member Profile Picture
    on 04 Aug 2020 at 16:00:30
    Re: Deleting items from Sharepoint List that are not found in Excel Table

    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

  • Jcook Profile Picture
    7,779 Most Valuable Professional on 03 Aug 2020 at 16:47:33
    Re: Deleting items from Sharepoint List that are not found in Excel Table

    @Anonymous 

     

    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.

  • Jcook Profile Picture
    7,779 Most Valuable Professional on 03 Aug 2020 at 16:21:51
    Re: Deleting items from Sharepoint List that are not found in Excel Table

    Hi @Anonymous 

     

    Try switching the values around.

     

    Current item contains Array

  • Community Power Platform Member Profile Picture
    on 03 Aug 2020 at 08:42:17
    Re: Deleting items from Sharepoint List that are not found in Excel Table

    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

  • SaiKrishnaGudluru Profile Picture
    499 on 01 Aug 2020 at 23:15:11
    Re: Deleting items from Sharepoint List that are not found in Excel Table

    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

     

  • Community Power Platform Member Profile Picture
    on 01 Aug 2020 at 21:17:49
    Re: Deleting items from Sharepoint List that are not found in Excel Table

    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

  • Verified answer
    Jcook Profile Picture
    7,779 Most Valuable Professional on 31 Jul 2020 at 21:12:38
    Re: Deleting items from Sharepoint List that are not found in Excel Table

    @Anonymous 

     

    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. 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Telen Wang – Community Spotlight

We are honored to recognize Telen Wang as our August 2025 Community…

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Tomac Profile Picture

Tomac 986 Moderator

#2
stampcoin Profile Picture

stampcoin 699 Super User 2025 Season 2

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 577 Super User 2025 Season 2

Loading complete