Replace column row value in datatable based on condition using linq query

Hi all,
I would need help like to replace value based on 2 columns.
If ColA row = “Apple” and ColB row = “Mango”, then I’d need to replace my row of a particular column with “Grape”. I’m very well aware of how for each work, I need a linq query as I have data more than 300k. I have many other columns in the datatable as well. Is there a query that would work exactly as for each row in datatable without modifying the datatable structure? i can’t use invoke code as it’s prohibited in my organization.

Hi @niro,

dt1.AsEnumerable().ToList().ForEach(Sub(row) row(“ColC”)= If(row(“ColA”).ToString.Equals(“Apple”) AndAlso row(“ColB”).ToString.Equals(“Mango”),“Grape”,row(“ColC”).tostring))

Try this in invoke code activity. Make dt1 as in/out argument.

Hope this helps.


1 Like

Hi @niro

Make it simple, just use a for each row in datatable, validate with if condition like this


Hi @niro

Try this code-

For Each row In dt.Rows
If row(“ColA”).ToString = “Apple” And row(“ColB”).ToString = “Mango” Then
row(“DesiredCol”) = “Grape”
End If

thank you @Harshith_Adyanthaya . how do i use the same query to copytodatatable instead of invoke code which is not allowed in my organization?

Hi @niro,

Could you please try the following query.

dt.AsEnumerable.Select(Function(r) dt.Clone.LoadDataRow({r(“ColA”).tostring,r(“ColB”).ToString, If(row(“ColA”).ToString.Equals(“Apple”) AndAlso row(“ColB”).ToString.Equals(“Mango”),“Grape”,row(“ColC”).tostring)},False)).CopyToDataTable()


hi @Harshith_Adyanthaya it’s prompting error that row is not declared

Replace row with r.

dt.AsEnumerable.Select(Function(r) dt.Clone.LoadDataRow({r(“ColA”).tostring,r(“ColB”).ToString, If(r(“ColA”).ToString.Equals(“Apple”) AndAlso r(“ColB”).ToString.Equals(“Mango”),“Grape”,r(“ColC”).tostring)},False)).CopyToDataTable()

@niro Hi. Try this way. Add 2 assign activities as shown in image. The value to save in 2nd assign statement should be
(From row In dt1.AsEnumerable
Let x= If( row(“ColA”).tostring.Equals(“Apple”) AndAlso row(“ColB”).tostring.Equals(“Mango”), “Grape”,row(“ColC”).tostring)
Let y= New Object(){ row(“ColA”),row(“ColB”),x}
Select dt2.rows.add(y)).CopyToDataTable

it doesn’t work fully as i have many other columns in the datatable. it don’t work the same as how for each behavior would without modifying the datatable structure

If columns are always same then we can add it to that query.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.