Filtering data table column values based on a list

[TyraS (Tyra)]

Have a list of strings to filter the data table starts with.
Any way other than creating multiple OR condition in the Filter Datatable activity?

List = {A,B,C,D}

Have to filter column1 of the datatable where values starts with A, or B, or C, or D

[V_Roboto_V]

Hi @TyraS

Could You try the following code:

dt_filtered  =  dtInput.AsEnumerable().
    Where(Function(row) prefixes.Any(Function(p) row("Column1").ToString().StartsWith(p))).
    CopyToDataTable()

Here, “prefixes” is the List

SAMPLE Demo:

Input:

The Output:

If this solves your issue, Do mark it as a solution
Happy Automation

[postwick (Paul Ostwick)]

Note that when using these expressions to filter datatables, you don’t have to create second datatable to filter into. You can just do…

dt_Input  =  dtInput.AsEnumerable().
    Where(Function(row) prefixes.Any(Function(p) row("Column1").ToString().StartsWith(p))).
    CopyToDataTable()

[V_Roboto_V]

True.

But I’m not aware of the Use Case. In cases where the main Data table will be used further down the workflow, then it is important that the filtered data table is stored separately.

Else, I totally agree. It can be stored in the same variable

[postwick (Paul Ostwick)]

@V_Roboto_V

But I’m not aware of the Use Case. In cases where the main Data table will be used further down the workflow, then it is important that the filtered data table is stored separately.

The use case would be where you will use the main datatable further down the workflow and need it to be filtered. Say you read an Excel file and it has records for 5 states but you’re only supposed to process 1 state. No need for a separate datatable, just filter out the states you don’t want. There are many use cases for doing this.