Delete rows in datatable based on values in list

[Katerina_Chabova (Kateřina Chábová)]

Hi, I am having a table with format

PO cus
1 aa
1 ef
2 wer
22 fds
4 bb
and list PO_list = (3,2,66,1)
I need to delete all rows which contains values from list.

[Srini84 (Srinivas)]

@Katerina_Chabova

Check below post for your reference

Filter data table wizard with array /List

Hi , my question how can you use the filter data table wizard with array or list? [image] I have a column “workercode” and i want to filter that column based on a array called code.

Hope this may help you

Thanks

[prasath_S]

Hi @Katerina_Chabova

Please try this,

Datatable variable name - dt
List variable name - PO_list

Query to remove rows based on list

dt.asenumerable.where(function(d) not PO_list.any(function(x) x.tostring.equals(d.tostring))).copytodatatable

Thanks

[Katerina_Chabova (Kateřina Chábová)]

It is not working, It just show entire table, filter doesn’t work. Shouldn’t there by any specific column from table, which is used for filtering?

[Katerina_Chabova (Kateřina Chábová)]

I tried this

(From r In datatable.AsEnumerable From s In list where r(0).tostring.Contains(s) Select r).CopyToDataTable

I need the exact opposite of this. Somehow combine the not contains function

[prasath_S]

PLEASE TRY THIS

dt.asenumerable.where(function(d) not PO_list.any(function(x) x.tostring.equals(d(“PO”).tostring))).copytodatatable

[ppr (Peter)]

PO_list = {3,2,66,1}

assign activity:
LHS: Result | DataType: List(Of DataRow)
RHS:

From d in dtData.AsEnumerable
Where Not PO_List.Any(Function (x) x.toString.Equals(d(0).toString.Trim))
Select r=d).toList

then check within an if acitvity
Result.Count > 0
Then: dtResult = Result.CopyToDataTable
Else: dtResult = dtData.Clone

[Katerina_Chabova (Kateřina Chábová)]

I’ve got error in if statement: Job stopped with an unexpected exit code: 0xE0434352, Exception Type: System.Exception

[ppr (Peter)]

A hint of excel issue

Job stopped with an unexpected exit code: 0xE0434352

Users with excel workflows accessing large amounts of data will encounter this error "Job stopped with an unexpected exit code: 0xE0434352", from time to time. Follow the below steps: Stop excel workflow temporarily and run a different workflow to see if the error still occurs. Make simple excel workflow that just accesses the data in the same way to see if error still occurs. Use kill excel Com object before reading excel object. Use retry scope or increase the timeout to 60-90 seconds to ens…

[Katerina_Chabova (Kateřina Chábová)]

OK I used Clear data table, but now it show me this error
the source contains a detached datarow that cannot be copied to the datatable

[ppr (Peter)]

please share a screenshot of this part implementation. thanks

[Katerina_Chabova (Kateřina Chábová)]

Assign statement and then If statement

I think the problem is PO_only3_dtrow in list of datarow .

[ppr (Peter)]

variables & flow:

LINQ:

(From d In dtData.AsEnumerable
Where Not arrPOFilter.Any(Function (x) x.toString.Equals(d("PO").toString.Trim))
Select r=d).toList

Find starter help here:
Katerina_Chabova.xaml (8.4 KB)