Distinct rows which allows null

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!

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!

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!

1 Like

@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

grafik

(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:

2 Likes

Thanks @ppr for this neat solution. :grinning:

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?

@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:

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

Yourdatatablevar.copytodatatable

Thanks so much guys for the suggestion and solution. :grinning:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.