cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Filtering a table in a spreadsheet using a SharePoint list

Documents/Files overview:

Master spreadsheet containing 3000+ tasks for various projects

Sharepoint list of 40 different project numbers that are important to my team

Refined spreadsheet containing only tasks assigned to the relevant project numbers

 

Current Operations:

1. Wipe the Refined spreadsheet

2. Get the most recent project number list from the SharePoint

3. List all of the rows in the Master spreadsheet (unable to perform OData filtering using the values from the sharepoint list apparently)

4. On a row by row basis (apply to each) compare that row's Project number to the SharePoint list by looping through an if statement.

5. If the current row's project number = a project number in the SharePoint list then add the current rows values as a new row to the refined sheet

 

Problem:

After 20 minutes it's on row 60/3637 and is obviously far too slow to be practical.  How can I refine/replace this process to speed it up?

 

Edit:

My best guess is that every time it adds a row it opens, updates, saves and closes the spreadsheet (as evidenced by the fact that I can open the spreadsheet and see that it has been updated throughout the process). A solution would be finding a way to store the rows and add them later all at once.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Filtering a table in a spreadsheet using a SharePoint list

Hi @seedorfjwork,

 

I think you can optimize it in step 5.

"If the current row's project number = a project number in the SharePoint list then add the current rows values as a new row to the refined sheet"

Before the loop, initialize an array variable to store these rows. If the current row's project number = a project number in the SharePoint list, append current rows into the array variable.  After the loop through is over, loop through the array and add the rows to the target Excel file.Annotation 2020-06-03 144415.jpg

 

If so, there is no need to consider the conflict with add multiple rows in the same line, a tip could make the loop through run faster is that you could open the Concurrency control of the Apply to each action:
Annotation 2020-06-03 144514.jpg

 

Annotation 2020-06-03 144459.jpg

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Dual Super User III
Dual Super User III

Re: Filtering a table in a spreadsheet using a SharePoint list

Hi!

I think you can improve performance from step 4: "On a row by row basis (apply to each) compare that row's Project number to the SharePoint list by looping through an if statement".

 

My suggestion is

1) to add an array withSharepoint project number. Select is much more efficient than 'Apply to each for such purpose'

2) to add an 'Filter array' assigning as its input your Master spreadsheet, then:

-assign to the left side of your condition rule 'Select' output

-assign to the right side of your condition rule current Master spreadsheet row 'Project id'

-operator 'contains'

3) to add an 'Apply to each' using as its input 'Filter array''s output, so you just iterate over your target projects, and not all the ones in the Master spreadsheet.

 

IN order to implement step 1), I would suggest the approach described here. The trick is to switch to Text Mode:

https://powerusers.microsoft.com/t5/Building-Flows/Create-a-variable-that-extracts-an-email-address-...

 

Hope this helps

 

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Community Support
Community Support

Re: Filtering a table in a spreadsheet using a SharePoint list

Hi @seedorfjwork,

 

I think you can optimize it in step 5.

"If the current row's project number = a project number in the SharePoint list then add the current rows values as a new row to the refined sheet"

Before the loop, initialize an array variable to store these rows. If the current row's project number = a project number in the SharePoint list, append current rows into the array variable.  After the loop through is over, loop through the array and add the rows to the target Excel file.Annotation 2020-06-03 144415.jpg

 

If so, there is no need to consider the conflict with add multiple rows in the same line, a tip could make the loop through run faster is that you could open the Concurrency control of the Apply to each action:
Annotation 2020-06-03 144514.jpg

 

Annotation 2020-06-03 144459.jpg

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
FirstImage

Microsoft Ignite 2020

Check out the announcement of Power Platform content at Microsoft Ignite!

thirdImage

Experience what's new for Power Automate

Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,956)