Get distinct values from excel where it meets a condition

Hey everyone,

I have an excel document which consists of 9/10 column. 2 of those columns are named “ProductNumber” & “DelDate” .

What I want to do is get the distinct Product Numbers (distinct, because there are some duplicates on the Prod.Num) , where there is no delDate given.

so:
Prod.Num/./././././DelDate
11223344/./././././
11223344/./././././
22334455/./././././12.12.2020
22334455/./././././12.12.2020
1111111111/./././././
0000000/./././././05.01.1999

So I want to get the values 11223344 & 111111111 and store it into an array for further processing.

I appreciate your help.
Ty,

Try this one
(From x in yourDT.AsEnumerable
Where isNothing(x(“DelDate”)) OrElse String.IsNullOrWhiteSpace(x(“DelDate”).ToString)
Let num = x(“ProductNumber”).ToString
Select num).Distinct.ToArray

Hi @jntrk, If you like to use Filter Datatable activity, then you could use this:

  1. Filter Data Table → Column = "DelDate" Condition "Is Empty" and Keep rows

  2. Second step assign an OutputArray =
    OutputDatatable.AsEnumerable().Select(Function (a) a.Field(of string)(“Prod.Num”).ToString).ToArray()

Suggested workflow with annotations: FilterDatatable.xaml (9.4 KB)

Thank you this is working,

And also to do the opposite job (get the prod.nums with delDate), what kind of modifications should I do on your code

This also makes sense thank you I’ve never thought about it :slight_smile:

reference following code

dt.AsEnumerable().
	Where(Function(r) r.IsNull("DelDate") Or String.IsNullOrWhiteSpace(r("DelDate").ToString)).
	Select(Function(r) r("Prod.Num")).Distinct.ToArray

The line with where is conditions for blank deldate.

Where Not String.IsNullOrWhiteSpace(x(“DelDate”).ToString)