Remove Duplicate row in Data table

[Sathish_Kumar_S]

Can you guide to achieve following requirements

1. How to remove the row if Language Column is EMPTY
2. if duplicate value found in “Key” Column - Keep only the row which has “en” as value in language column

Example : Keep only 39th row and delete 37 & 38

3. “RowNo” column value + 2 and add another column and update the result

Example : New column name "New Row No : the value will of 38th row is 36 and result would be 38 in “New Row No” column

[pavithra_pavi]

Hello @Sathish_Kumar_S , Please refer this YouTube video

Get duplicates and non duplicate rows using Uipath

[fernando_zuluaga]

Hi @Sathish_Kumar_S

Kindly provide your expected output based on the input

Regards

[Yoichi]

Hi,

How about the following?

How to remove the row if Language Column is EMPTY

dt1 = dt.AsEnumerable.Where(Function(r) not String.IsNullOrEmpty(r("Language").ToString)).CopyToDataTable()

if duplicate value found in “Key” Column - Keep only the row which has “en” as value in language column

dt2 = dt.AsEnumerable.GroupBy(Function(r) r("Key").ToString).SelectMany(Function(g) g.Where(Function(r) r("Language").ToString="en")).CopyToDataTable()

“RowNo” column value + 2 and add another column and update the result

dt3 = dt.AsEnumerable.Select(Function(r,i) dt.Clone.LoadDataRow({r(0),r(1),if(String.IsNullOrEmpty(r(1).ToString),"",i.ToString)},false)).CopyToDataTable

Sample20230512-2L.zip (3.0 KB)

Regards,

[Sathish_Kumar_S]

@fernando_zuluaga @Yoichi

Attached the input file for DT & expected output files . Kindly review the file and provide the solution… thanks

Expectedoutput.xlsx (8.5 KB)
Input-DT.xlsx (8.6 KB)

[fernando_zuluaga]

Hi @Sathish_Kumar_S

@Yoichi already provided the solution, kindly give a try and let us know if you got the expected output

Regards!

[Sathish_Kumar_S]

Hi I have noticed the code is removing “en” row for non duplicate rows also

For example

PV3.11.3 & PV 3648 has “de” as value and it has removed in the output

We need to remove “de” only if we have duplicate value in KEY column

Output

[Yoichi]

Hi,

Can you try the following?

 dt = dt.AsEnumerable.Where(Function(r) not String.IsNullOrEmpty(r("Language").ToString)).GroupBy(Function(r) r("Key").ToString).Select(Function(g) g.OrderByDescending(Function(r) arr.Contains(r("Language").ToString)).First).CopyToDataTable

Next add new column, then

dt = dt.AsEnumerable.Select(Function(r) dt.Clone.LoadDataRow({r(0),r(1),r(2),Cint(r(2))+2},False)).CopyToDataTable()

Sample20230512-2Lv2.zip (15.1 KB)

Regards,