CopyToDataTable is not a member of System.Data.Datatable

[hurmet.noka]

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

This is so annoying…
Thank you guys for your help

[Veera_Raj (Veeraraj Sethuraman)]

you can use groupby for specific column

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

[hurmet.noka]

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

[Veera_Raj (Veeraraj Sethuraman)]

can you give sample input and expected output ?

[hurmet.noka]

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,,"

[kumar.varun2 (Varun Kumar)]

Hi @hurmet.noka

Try this

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

[Veera_Raj (Veeraraj Sethuraman)]

can you try with remove duplicate range activity?

[hurmet.noka]

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

[hurmet.noka]

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

[kumar.varun2 (Varun Kumar)]

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

[Rajamohan_Balmuri]

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

[hurmet.noka]

AMAZING, Thank you so much!

[ppr (Peter Preuss)]

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

@kumar.varun2

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

@hurmet.noka

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

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:

AsEnumerable is not a member of 'System.Data.Datatable'

Hi @lucas.mcgonagle EDIT 20.10 fixed this issue for newly created projects. You can fix it by adding the Assembly Reference manually in your xaml file: [image] Simply open the file in Notepad and create new line with this String: <AssemblyReference>System.Data.DataSetExtensions</AssemblyReference> Then reopen your project and the error should be gone. Please be careful while doing so and always keep a backup.