cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
seedorfjwork
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
v-litu-msft
Community Support
Community Support

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
efialttes
Super User III
Super User III

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!



v-litu-msft
Community Support
Community Support

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
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

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

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Users online (50,020)