Filter DataTable-The output row count is the same as input

I’m trying to remove rows in excel that their 4th column starts with: 100 or 200.
Note that in the sheet those cells appear with a space.
The Output sheet row-count is the same as the input…
See image:

Thank you!

Hi @hp321 ,

You should provide the Column Index and not the Column number, If your Column Number is 4 then 3 should be provided in the column section.

Indexing starts from 0.

1 Like

Have a look at this LINQ

Assign Activity
dtFiltered =

(From d in YourDataTable.AsEnumerable
Let v = d(3).toString.Trim
Let chk = {"100","200"}.Any(Function (x) v.StartsWith(x))
Where not chk
Select r = d).CopyToDataTable

Handling empty result:
:ambulance: :sos: [FirstAid] Handling of The source contains no DataRows exception - News / Tutorials - UiPath Community Forum

Debugging / Inspection / Tracing / Prototyping
Understanding the 6 Debugging Panels of UiPath in the easiest way possible! - News / Tutorials - UiPath Community Forum

EDITED: with the hint of @supermanPunch
changed 4 to 3


use this in assign activity

dt1.AsEnumerable.Where(Function(a) not a(3).ToString.trim.StartsWith("100") or a(3).ToString.trim.StartsWith("200")).CopyToDataTable

no need to use filter activity

Hi @Shiva_Nikhil
I tried you idea. As you can see:
it is index 4 [column 5]
I would want that something like this won’t appear:
I assigned this to the dt variable:

Do I need to add the space? like (" 200")?

as we do trim the column values to be more robust we dont need the space for " 100", " 200" StartsWith check and can handle both scenarios (with/without starting space in column)

Thanks @ppr
See what I get:

we dont know what was done at your end in details

But we do have this result and confirmation that is accepted within an Assign Activity

I’m trying to keep the rows that in the 5th column are empty or are “N/A” or contain “2023”.
I started the workflow with “read range” added the “assign” and concluded with “write range” using the same variable at all stages.

I’m using this code in assign:

(dt_filteredjoin.AsEnumerable().Where(Function(x) x(4).ToString.Contains(“2023”) Or x(4).ToString.Equals(“N/A”) Or String.IsNullOrEmpty(x(4).ToString))).CopyToDataTable()

Please let me know the reason to that he column comes out empty,althouhg when I click to check the count I see there are a few…
Thank you!

Thanks @supermanPunch , @ppr , @Shiva_Nikhil
The sheet was not deleted after each run, that’s why it seemed like the filter was not working properly…