Remove duplicated rows - in selected range

[E.T.S]

I am using the code to remove duplicated rows in my data table:

FilteredDataTable = DataTable1.AsEnumerable().Except(DataTable2.AsEnumerable(), DataRowComparer.Default).CopyToDataTable()

Please consider this:

Table a -

Comparing to table b -

I would like the bot to remove the row with the duplicates that are in column A:G

With the above code it will return no duplicates as column H and I will be different. Is there a way of configuring the code to say if in the table A:G are the same delete the entire row (including columns H and I)?

Cheers

[rlgandu (Rajyalakshmi Gandu)]

@E.T.S

FilteredDataTable = (From row1 In DataTable1.AsEnumerable()
                    Where Not DataTable2.AsEnumerable().Any(Function(row2) row1("A").ToString() = row2("A").ToString() AndAlso
                                                                     row1("B").ToString() = row2("B").ToString() AndAlso
                                                                     row1("C").ToString() = row2("C").ToString() AndAlso
                                                                     row1("D").ToString() = row2("D").ToString() AndAlso
                                                                     row1("E").ToString() = row2("E").ToString() AndAlso
                                                                     row1("F").ToString() = row2("F").ToString() AndAlso
                                                                     row1("G").ToString() = row2("G").ToString())
                    Select row1).CopyToDataTable()

Hope this query works for you

[pikorpa (Piotr Kołakowski)]

Hey @E.T.S
I’m not sure if I understood correctly your case, but please check this solution:
BlankProcess100 (2).zip (15.9 KB)

[E.T.S]

Thank you for your reply!

Would I need to use this in an assign?

Cheers

[supermanPunch (Arpan)]

Hi @E.T.S ,

Could you maybe check with the below Post :

Filter DataTable1 by excepting rows from DataTable2 by comparing specific column

give a try at dtResult = (From d in dt1.AsEnumerable Let raf = d.ItemArray.Take(8) Let chk = dt2.AsEnumerable.Any(Function (d2) d2.ItemArray.Take(8).SequenceEqual(raf)) Where not chk Select r =d).CopyToDataTable Assumption: both dts are defining the same datacolumns and its DataTypes no trims needed Empty Filterresult handling:

For your case, we can dynamic the G column number by using the Expression below :

ColumnTake = UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnLetterToColumnIndex("G")

Here, ColumnTake is an integer variable.

Then modifying the Expression in the post we get :

FilteredTable = (From d in dt1.AsEnumerable
Let raf = d.ItemArray.Take(ColumnTake).ToArray
Let chk = dt2.AsEnumerable.Any(Function (d2) d2.ItemArray.Take(ColumnTake).ToArray.SequenceEqual(raf))
Where not chk
Select r =d).CopyToDataTable

Also, to handle the Exception when no rows are filtered we can follow the below :

[FirstAid] Handling of The source contains no DataRows exception

This FirstAid Tutorial will describe how a the source contains no DataRows EXCEPTION can be handled. Introduction Let’s have a look at the following filter data table scenario: Name CCode Tom US Charlotte FR Seema IN Jean FR Assignment: Filter all rows on a particular Country Code like FR, UK, ES, UK… Ensure that all CCode data column values are trimmed Ensure that the Filter check is case insensitive we can implement it e.g. with the help of a LINQ statement: dtData.AsE…

Note : The assumption is the Headers are not present for the data, hence the Read Range activity Add Headers property are unchecked.

[rlgandu (Rajyalakshmi Gandu)]

@E.T.S

FilteredDataTable = (From row1 In DataTable1.AsEnumerable()
                    Where Not DataTable2.AsEnumerable().Any(Function(row2) row1("A").ToString() = row2("A").ToString() AndAlso
                                                                     row1("B").ToString() = row2("B").ToString() AndAlso
                                                                     row1("C").ToString() = row2("C").ToString() AndAlso
                                                                     row1("D").ToString() = row2("D").ToString() AndAlso
                                                                     row1("E").ToString() = row2("E").ToString() AndAlso
                                                                     row1("F").ToString() = row2("F").ToString() AndAlso
                                                                     row1("G").ToString() = row2("G").ToString())
                    Select row1).CopyToDataTable()

I hope this will work for you

[E.T.S]

Hi,

Thanks for your flow!

Please could you tell me what modification to the code do I need to do if I am looking at matches in columns A to I

Cheers

[pikorpa (Piotr Kołakowski)]

@E.T.S
By expanding the Tuple.Create method to include r(7) and r(8) , the query now groups rows based on the first nine columns (A to I), Like this:
dtCombined.AsEnumerable().GroupBy(Function(r) Tuple.Create(r(0), r(1), r(2), r(3), r(4), r(5), r(6), r(7), r(8))).Select(Function(g) g.First()).CopyToDataTable()

[E.T.S]

Hi,

Thanks for your reply! Even though I have my variables I am getting this error:

[E.T.S]

I am getting this error:

[pikorpa (Piotr Kołakowski)]

for invoke code you need to pass arguments:

[pikorpa (Piotr Kołakowski)]

@E.T.S
so, maybe try this
dtCombined.AsEnumerable().GroupBy(Function(r) New With {Key .Col0 = r(0), Key .Col1 = r(1), Key .Col2 = r(2), Key .Col3 = r(3), Key .Col4 = r(4), Key .Col5 = r(5), Key .Col6 = r(6), Key .Col7 = r(7), Key .Col8 = r(8)}).Select(Function(g) g.First()).CopyToDataTable()

[E.T.S]

Hello,

I have created my arguments but am getting this error:

[E.T.S]

Hello,

That doesn’t seem to remove the duplicates unfortunately

[rlgandu (Rajyalakshmi Gandu)]

@E.T.S

Sequence6.zip (2.0 KB)
Please find the attached process hope it works for you

[rlgandu (Rajyalakshmi Gandu)]

@E.T.S

Please tell me if my workflow is working for you or i will try another approach

[E.T.S]

Awesome this works! Thanks so much for your help!