Need Help ! Want to update Column Values in the same sheet without using For each

Help Studio
, , , ,
1

I’m facing one problem for very long, please help me experts.

  1. I have a large chunk of data in the .xlsx file (sheet1) which I need to manipulate faster.
  2. I was able to achieve the required results with For each, but it is taking 10 hours to finish on thousands of entries.
  3. I would like to know the approach with either power Query, Linq or any other methods. Please Solve the example below;

3.1) I have three columns in Excel Sheet1;
Mat group, Tech code, Industry Domain
FG, AA, 10
FG, BB,
FG, CC, 11

3.2) I need to Filter and Update below records in the same Excel Sheet1 like this;
FG, AA, 10
RL, AA, 10
RL, AA, 10
FG, BB,
RL, BB,
FG, CC, 11
RL, CC, 11
RL, CC, 11

3.3) Basically I need to update RL rows having same tech code as FG rows with values of Industry Domain from FG, please refer images attached also.

Bot is taking forever to update using for each, please urgently help.
Regards

Screenshot (176)
Screenshot (176)1920×1080 116 KB

Screenshot (178)
Screenshot (178)1920×1080 118 KB

2

Hi! I think there is no problem if you use a ForEach activity, the key resides in the way you writing data within the excel file. If you use write range too many times it would take forever. My suggestion is to use a read range activity and then made the changes required over the data table. Once the data table is completed then use a write range activity and set as an input a full column of a data table - so you have to use one write range per column. I hope this help

3

I’m not using too many write range, only one in the end. But if condition is taking time

4

Ok. Could you share the if sequence?