How to group a datatable based on unique values

Help Studio
, ,
1
System Name System Acronym PM In-Charge Product Type Frequency Scope Status Status Code
Test System TS Tan VAPT 12 0 SUCCESS 20230202
Test System TS Tan RR 24 0 FAIL FILE_NOT_FOUND
Prod System PS Lim VAPT 12 0 FAIL FILE_NOT_FOUND

Hi,

How do I get the unique values (system name) based on the table above? with all columns and records retained

2

Hello @sophiey
can you tell me you just want for each uniquevalue in syste name or whole table at one go.

3

@sophiey

dt=dt1.AsEnumerable.GroupBy(function(x) x(0).ToString).Select(function(r) r.First).copytodatatable

cheers

4

@sophiey

DT1.AsEnumerable.where(function(x) x(“ColumnName”).ToString.Equals(“yourSystemName”)).copytodatatable

5

whole table at one go.

6

i’ve tried this, it only returned 1 record (Test System) whereas there are 2 records under Test System

7

Hi @sophiey

Try this:

uniqueRows = (From row In dtData.AsEnumerable()
                  Group row By systemName = row.Field(Of String)("System Name") Into systemGroup = Group
                  Select systemGroup.First()).CopyToDataTable()

Hope it helps

8

can i check what does First() do? i want to have all records under same systemName retained

9

Hi @sophiey
Explanation:

From row In dtData.AsEnumerable(): This part of the query converts your DataTable dtData into an enumerable collection of DataRow objects, allowing you to iterate over each row in the DataTable.

Group row By systemName = row.Field(Of String)(“System Name”) Into systemGroup = Group: Here, it groups the rows based on the value in the “System Name” column. It assigns the name systemName to each unique value in the “System Name” column, and it groups the rows with the same “System Name” value together, which results in a collection of groups.

Select systemGroup.First(): Within each group, it selects the first row. This is done because you want to retain all columns for each unique “System Name,” so selecting the first row from each group effectively keeps all the columns for each unique value in the “System Name” column.

.CopyToDataTable(): Finally, the CopyToDataTable method is used to convert the resulting collection of DataRow objects back into a new DataTable. This new DataTable contains the unique values in the “System Name” column while retaining all other columns from the original DataTable.

or use this syntax:

uniqueRows= (From row In dtData.AsEnumerable()
                  Group row By systemName = row.Field(Of String)("System Name") Into systemGroup = Group
                  Select systemGroup).SelectMany(Function(systemGroup) systemGroup).CopyToDataTable()

Hope it helps

10

@sophiey

If you want only unique records considering whole row…then please use remove duplicate rows activity

https://docs.uipath.com/activities/other/latest/workflow/remove-duplicate-rows

Cheers

11

@sophiey

Can you provide what was your expected output

Cheers

12

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