How to split datatable based on Group by and values in a column in Linq Query? Please Help

I need to split datatable into two datatables based on Group by and another column values.
From the Source Datatable, I need to move records based on Group by columns “Col 1” and “Col 2”. In addition, if the values in the “Col 3” contains “I” & “A”, then the set of records should be moved to new datatable (DT1). And rest of the Source Datatable should be moved to another new datatable (DT2). Please help me to solve this.

What I have:
Source Datatable:
image

What I need:
In a New Datatable (DT1):
image

and
In a New Datatable (DT2):
image

Since dealing with datatable with large data, I am trying in LinQ Query instead of For Each loop:

Please help me

Hi @GuhanTM

Are you searching for the below solution ??

Input :-

image

Linq for DT1 :-

(from d in readExcel.AsEnumerable Where (d(0).ToString.Equals("One") And d(1).ToString.Equals("10")) Or (d(0).ToString.Equals("Two") And d(1).ToString.Equals("20")) Select d).CopytoDatatable

Output :-

image

Linq for DT2 :-

(from d in readExcel.AsEnumerable Where (d(0).ToString.Equals("One") And d(1).ToString.Equals("20")) Or (d(0).ToString.Equals("Two") And d(1).ToString.Equals("10")) Select d).CopytoDatatable

Output :-

image

Below is the workflow for the same :-
MainPratik.xaml (10.3 KB)
New Microsoft Excel Worksheet.xlsx (8.5 KB)

If this is the solution then

Mark as solution and like it if this helps you :slight_smile:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

@Pratik_Wavhal Thanks for the reply.
But what I shared is sample data only and the actual input is huge data with more columns.

@GuhanTM
find starter help here:
Guthan_SplitDT.xaml (10.1 KB)

2 Likes

@ppr Thank you so much for the solution.
It is working as expected.
Thanks again.

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