CopyToDataTable is not a member of System.Data.Datatable

image

Can you please help?
I want to remove duplicates based on only TWO columns.

This is so annoying…
Thank you guys for your help

you can use groupby for specific column

outputDataTable = dt.AsEnumerable().GroupBy(Function(x) x(“ColName”).ToString)[Select](Funtion(y) y.First()).CopyToDataTable()

1 Like

I want to filter by TWO columns not just one
Thank you

can you give sample input and expected output ?

INPUT

**id,Description,Seller Message,Created (UTC),Amount,Amount Refunded,Currency,Converted Amount,Converted Amount Refunded,Fee,Tax,Converted Currency,Status,Statement Descriptor,Customer ID,Customer Description,Customer Email,Captured,Card ID,Invoice ID,Transfer**
"ch_3LP8IuGl4fCZ9r4x0fjKBphN,JFR624 Transfer,Payment complete.,2022-07-24 17:11,""600,00"",""0,00"",eur,""600,00"",""0,00"",""15,25"",""0,00"",eur,Paid,,,,,true,src_1LP8JtGl4fCZ9r4x2BrrMseX,,"
"ch_3LP0leGl4fCZ9r4x1bfsf9vf,VFX837,Payment complete.,2022-07-24 09:07,""3500,00"",""0,00"",eur,""3500,00"",""0,00"",""101,75"",""0,00"",eur,Paid,,,,,true,src_1LP0mdGl4fCZ9r4xiUkSFrJq,,"
"ch_3LP0hXGl4fCZ9r4x0SL6cbP1,VFX837,Payment complete.,2022-07-24 09:03,""3500,00"",""0,00"",eur,""3500,00"",""0,00"",""101,75"",""0,00"",eur,Paid,,,,,true,src_1LP0iNGl4fCZ9r4xvMhRr3Bu,,"
"ch_3LP0fpGl4fCZ9r4x08oxGrmm,VFX837,Payment complete.,2022-07-24 09:02,""3500,00"",""0,00"",eur,""3500,00"",""0,00"",""101,75"",""0,00"",eur,Paid,,,,,true,src_1LP0ggGl4fCZ9r4xZQVdIwzz,,"
"ch_3LP0duGl4fCZ9r4x0dVfPfLL,VFX837,Payment complete.,2022-07-24 09:00,""3500,00"",""0,00"",eur,""3500,00"",""0,00"",""101,75"",""0,00"",eur,Paid,,,,,true,src_1LP0ezGl4fCZ9r4xuMhkEaqM,,"
"ch_3LP0ZfGl4fCZ9r4x0Pw72TII,VFX837,Payment complete.,2022-07-24 08:58,""3500,00"",""0,00"",eur,""3500,00"",""0,00"",""101,75"",""0,00"",eur,Paid,,,,,true,src_1LP0cVGl4fCZ9r4xW7TdSQ8y,,"
"ch_3LOkkpGl4fCZ9r4x17rQZRtS,RRF130,Payment complete.,2022-07-23 16:02,""149,00"",""0,00"",eur,""149,00"",""0,00"",""2,34"",""0,00"",eur,Paid,,,,,true,src_1LOkmFGl4fCZ9r4xWRwIok78,,"

Output:

*id,Description,Seller Message,Created (UTC),Amount,Amount Refunded,Currency,Converted Amount,Converted Amount Refunded,Fee,Tax,Converted Currency,Status,Statement Descriptor,Customer ID,Customer Description,Customer Email,Captured,Card ID,Invoice ID,Transfer*
"ch_3LP8IuGl4fCZ9r4x0fjKBphN,JFR624 Transfer,Payment complete.,2022-07-24 17:11,""600,00"",""0,00"",eur,""600,00"",""0,00"",""15,25"",""0,00"",eur,Paid,,,,,true,src_1LP8JtGl4fCZ9r4x2BrrMseX,,"
"ch_3LP0leGl4fCZ9r4x1bfsf9vf,VFX837,Payment complete.,2022-07-24 09:07,""3500,00"",""0,00"",eur,""3500,00"",""0,00"",""101,75"",""0,00"",eur,Paid,,,,,true,src_1LP0mdGl4fCZ9r4xiUkSFrJq,,"
"ch_3LOkkpGl4fCZ9r4x17rQZRtS,RRF130,Payment complete.,2022-07-23 16:02,""149,00"",""0,00"",eur,""149,00"",""0,00"",""2,34"",""0,00"",eur,Paid,,,,,true,src_1LOkmFGl4fCZ9r4xWRwIok78,,"

Hi @hurmet.noka

Try this

dt_ExtractedData.DefaultView.ToTable(True, {"Description", "Amount"})

can you try with remove duplicate range activity?

1 Like

I want to DELETE Duplicate rows based on TWO columns, but i want to keep all other columns

image
I want to remove rows based on TWO columns only not all columns
This activity gets all columns.

@hurmet.noka

Please refer this xaml.
Removing the duplicate based on two columns only. The output will have all the rows.

RemoveDuplicateBasedOnTwoColumns.xaml (7.8 KB)

image

1 Like

DT.Asenumerable.Groupby(Funtion(r) Touple.Create(r(“Description”).Tostring,r(“Amount”).Tostring)).Select(Function(g) g.First).Copytodatatable

1 Like

AMAZING, Thank you so much!

Lets do it one by one

dt_ExtractedData.DefaultView.ToTable(True, Description, Amount) 

will conflict with the syntax as an array with the colnames is needed. It already returns a datatable and thats why copytodatatable is causing the issue. But it will return only the two columns, configured in the colname array

here you can use the LINQ provided by @kumar.varun2 or following variation

(from d in dt_ExtractedData.AsEnumerable
Group d by k1=d(“Description”).toString.Trim, k2=d(“Amount”).toString.Trim into grp=Group
Select r=grp.First()).CopyToDataTable

it will distinct the group members by taking the first group member.

For other scenarios e.g. fetch all unique groups we would filter with where grp.Count = 1

Ensure following:

1 Like

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