How to Group by and filter using LINQ or Uipath activities

Help Activities
, , ,
1

I want to do group by and filter. My requirement is i want to group by “Age” and get 1st occurrence but ignore if Age is “N.A.”.

Example Input :

The image shows a spreadsheet with columns for "Name," "Age," and "Job", containing entries of individuals with some duplicated names and roles. (Captioned by AI)
The image shows a spreadsheet with columns for "Name," "Age," and "Job", containing entries of individuals with some duplicated names and roles. (Captioned by AI)855×190 5.43 KB

Output :

The image is a table with a yellow header displaying three columns: "Name," "Age," and "Job," listing three people, where only James Bond's age is provided as 64 and he works in IT, and both James Watson and Sin Waltor's ages are marked as N.A and they work as QA Engineers. (Captioned by AI)
The image is a table with a yellow header displaying three columns: "Name," "Age," and "Job," listing three people, where only James Bond's age is provided as 64 and he works in IT, and both James Watson and Sin Waltor's ages are marked as N.A and they work as QA Engineers. (Captioned by AI)820×157 3.62 KB

Here as age is “N.A.”, I will ignore them from group by, but for age available is for a employee, then I take 1st occurrence if Age is same .

2

@Jena Use this linq

(From row In DT
Group row By a=row(“Name”).ToString.trim Into grp=Group
Select grp.last).CopyToDataTable

3

I want to group by “Age”, not name. If Age is “N.A.” then ignore in group by but those should be part of output excel.

5

@Jena

This image depicts a workflow for reading data from the "Input" sheet of "Excel1.xlsx", processing it, and then writing the processed data to the "InputRes" sheet within the same Excel workbook using UiPath activities. (Captioned by AI)

DT = (From row In DT.AsEnumerable()
Group row By key = row("Age").ToString() Into Group
From firstRow In If(key = "N.A", Group, Group.Take(1))
Select firstRow).CopyToDataTable()

Input:

A spreadsheet lists names, ages, and jobs of four individuals with some age entries marked as "N.A" and job roles including IT, QA Engineer, and RPA. (Captioned by AI)

Output:

The image shows a table of individuals with their names, ages, and job titles, where some age information is marked as "N.A". (Captioned by AI)

Regards,

6

Hi @lrtetala
can you pls elaborate the LINQ query, especially the 3rd row.
Thx

7

Hi @J0ska

Sure,

->Group row By key = row(“Age”).ToString() Into Group
This groups the rows by the value in the Age column

->From firstRow In If(key = “N.A”, Group, Group.Take(1))
If the key(Age) is “N.A”, it takes all rows in that group.
If the key(Age) is anything else, it takes only the first row of the group (Group.Take(1)).

Regards,

1 Like