Filtering datatable based on another data table column

I have a dt1 with columns ID and Name, i want to iterate over them and filter dt2 based on dt1 (ID, and name).dt2 has several other columns other than ID and Name.

Dt2 has large number of rows so i want to use approach other than “for each row in datatable”. And then write that filtered datatable

Hi @Sami_Rajput

What is your requirement to filter the dt2 data. Your query is quite confusing, be more specific if possible share the input data and expected output data.

Hi @Sami_Rajput ,

’ Assign the LINQ query to filter dt2 based on dt1
filteredDT = (From row2 In dt2.AsEnumerable()
Join row1 In dt1.AsEnumerable()
On row2.Field(Of String)(“ID”) Equals row1.Field(Of String)(“ID”) And
row2.Field(Of String)(“Name”) Equals row1.Field(Of String)(“Name”)
Select row2).CopyToDataTable()

’ Write the filtered DataTable to Excel
Write Range Activity
Input: filteredDT
Destination: “FilteredData.xlsx”

Regards
Sandy

1 Like

Basically iterating over Dt1 and checking the rows in Dt2 where ID and Name is equal to Dt1 ID and Name

And then writing whole filtered data we will get from dt2 into excel sheet
@mkankatala

Okay got it… @Sami_Rajput

let’s imagine Datatable 1 as dt1 and Datatable 2 as dt2. You can use the below LINQ Expression to get the filter data in dt2.

- Assign -> dt2 = (From a In dt2_Input.AsEnumerable() 
                    Join b In dt1_Input.AsEnumerable() 
                    On a("id").ToString() Equals b("id").ToString() And a("Name").ToString() Equals b("Name").ToString() 
                    Select a).CopyToDataTable()

Hope it helps!!

This is called a Join. Just use the Join Data Table activity set to inner join. The resulting table will have all the rows where the values match.

Hie, @Sami_Rajput i share the screenshot with you to how you can use linq function to check that two different excel column has same data or not .modify it with your requirment . if it help you .please marks this as Helpful. happy automation