How to replace a value in column without using For each loop

Hello All

I want to replace a value ie “0” present in multiple rows of a column name “Data” with “1”. As the No of rows are above 40000, using Iteration takes a lot of Time. Kindly Help me with Linq Query or Regex function.

1 Like

Hi @Ripusudan_Sharma

Can u show the input data and the output format?

for eg. a column in a table has 6 rows with values : 20, 33, 0, 3, 40, 0
i want to replace(0 by 1) to make it as column with values: 20,33,1, 40, 1.
Please provide a linquery to get the result for entire datatable.

@Ripusudan_Sharma - i have tried something like this…and it worked…but I am not sure how it will behave with 40K rows…so please try it and let me know…

My Input in “Sheet1”


XAML Image:


Output in Sheet2


Test with the code below. You need to use an Invoke Code activity and pass the data table as an argument. The argument is passed on as a reference since DataTable is an object so you shouldn’t see any performance penalty compared with an Assign activity. An assign activity won’t work because it doesn’t like multiline lambda function for some reason.


DT.Select("[Data] = 0").All(Function(r)
							r("Data") = 1
							Return True
						End Function)


DT.Select("[Data] = 0").All(r => { r["Data"] = 1; return true; });

The direction for the argument can be In or In/Out. It doesn’t matter here since it’s sent by reference.