Power Platform Community Forum Thread Details

Hi, My Power Automate Flow has to process data from 2 excel tables based on some criteria and update one table.

In one table (JobName), I have 600+ rows. Each row has a job name in a column.

In another table (Keyword), I have a list of 54 Key words.

The flow has to check the occurance of each key word from Keyword Table in each job name in JobName table and get the count of Jobs having that particular keyword and update the count of jobs having that key word in Keyword Table.

The sum of jobs count for all 54 keywords must match the total number of jobs.

Another condition or requirement is, one jobname must be counted against one key word only or in other words, if a key word is found in a Job Name, then that Job name should not be considered for subsequent keywords. So, as soon as I found a job name with a keyword and that jobname is counted, I update another column in JobName Table as Yes. To begin with this column will have No as default value for all 600+ records.

To give more info: For the 'Apply to each' action, I have set the concurrency control as 1. If I increase, I am not getting correct count for each keyword. Hence, I set it as 1.

When I test this flow, it runs for more than 4 hours, as it searches each of the 54 keywords in each of the 600+ jobnames. To be precise, 663 job names are searched for 54 keywords. So the loops run 663 x 54 = 35802 times, is my understanding.

May I know whether there is any way to complete the flow quickly? Even to do this data manually using excel formulas or VBA macro, it didn't take more than 30 to 40 minutes. I am sharing the screen shot showing loops from Run History.

Any guidance on this will be helpful. Thanks.