Manipulate Data Table Column With Formula

Help Studio
1

I need to apply a formula to an entire column and copy into a new datatable. I don’t want to use a For Each loop since sometimes these tables have a LOT of lines.

I have a column where negative values have the “-” at the end, so I need an if statement to parse and convert to double. The if is pretty straight forward, but I’m not sure how to phase the Enumerable expression
Before:

Col1 Col2
Text1 123
Text2 123-
Text3 1234
After:
Col1 Col2
— —
Text1 123
Text2 -123
Text3 1234
2

Before:
image

Logic:

First use read Range activity for getting all value from excel and stored in datatable variable like(Ex: dt_test)

image

then use For Each Row Activity with indexnum integer type and put If Condition in for each row

image
image1169×471 16.9 KB

then Use If Condition and condition is (row(1).toString.EndsWith(“-”))

Here I m using Row(1) for col 2.

Then use Write cell Activity for to get output like:

image

Thanks
Let me know still if you have any doubt

3

Hi, I said I don’t want to use a For Each activity. These tables can get really big and I’m looking for a more efficient way to do it.

4

@Wil_Lee - Please check the below

string.Format(“{0}{1}”, Str.Substring(Str.Length - Length),Str.Substring(0, Str.Length - Length))

image
image1112×423 16.2 KB

Output:

image

5

@Wil_Lee - Any update on this?

6

No this only transforms the string which is not the issue here. The original ask was how to apply an efficient transformation on an entire datatable column. Efficient being, not using a for each loop as sometimes these tables have 50,000+ rows.