Distinct rows which allows null

[mnlatam]

I have a datable wherein column ‘Country’ has duplicate values and can have null values.
I want to get distinct records which allow null values and store it to datatable.
I also want to store into DT variable those that were filtered out.

Appreciate your help. Thanks!

[mnlatam]

Hi @ppr sorry for tagging you. I saw your solution in another post regarding similar question as mine but with some conditions. Hope you can help me with this. Thanks alot!

[kadiravan_kalidoss (Kadiravan K)]

Hi @mnlatam ,

Please check this file distinctDt.xaml (11.7 KB)
Sample input: Input.xlsx (8.2 KB)
Generated output: Output.xlsx (8.1 KB)

Please let me know for any issues.

Thanks!

[ppr (Peter)]

@mnlatam
we can summarize the requirements as following:

• group data by Country:
  • take all group members when Country is empty
  • take first group member when Country is not empty
• get it ordered as by input order

(From d In dtInput.AsEnumerable
Group d By k=d("Country").toString.Trim Into grp=Group
Let  gf = grp.Where(Function (x) IsNothing(x("Country")) OrElse String.IsNullOrWhiteSpace(x("Country").toString.Trim)).toList
Let nr = If(gf.Count>0, gf, {grp.First}.toList)
From g In nr
Order By dtInput.Rows.IndexOf(g)
Select g).CopyToDataTable

dtOutput2 = dtInput.AsEnumerable.Except(dtOutput1.AsEnumerable, DataRowComparer.Default).CopyToDataTable

find starter help here:
mnlatam.xaml (8.3 KB)

apply following pattern for handling empty results:

[mnlatam]

Thanks @ppr for this neat solution.

For the second requirement wherein I need to get the duplicate values except the default, I’m encountering an error for dtOutput2 when I tried to removed the ID column from dtInput. I forgot to mention that my input DT doesn’t have a key ID. Do I need to add temporary primary key for my datatable? Is it because comparing of two DT relies on primary key?

[ppr (Peter)]

@mnlatam
in general the Except statement relies not a primary key definition
it looks like for any reason the result is empty,which cause this message. Apply following pattern to handle empty result:

[mnlatam]

@ppr
I see, May I know what’s the value of dtDuplicatesOnly in Then statement? Thank you so much

[ppr (Peter)]

Yourdatatablevar.copytodatatable

[mnlatam]

Thanks so much guys for the suggestion and solution.