DataTable filtering with expressions

Hi All,

Use below code to remove empty row from the table.
DataTable name->DataTableName
Use assign activity
DataTableName=DataTableName.Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or field.Equals(""))).CopyToDataTable()

Re-usable Component pass Parameter as datatable you will get the answer.

RemoveEmptyRows.xaml (8.5 KB)

Remove rows based on particular column is empty.
DataTableName.Select("Convert(column3, System.String)< >''").CopyToDataTable()
DataTableName.Select("column3< >''").CopyToDataTable()

Distinct Based on the specific column name from the data table and need to return the hole data.
Remove Duplicate record from the Data Table based on Column Name
dt.AsEnumerable().GroupBy(Function(i) i.Field(Of String)("ColumnName")).Select(Function(g) g.First).CopyToDataTable()
((From LineNo In dt.DefaultView.ToTable(True,"Product").Select().ToList() Select (From row In dt.Select Where row("Product").ToString=LineNo("Product").ToString Select row).ToList(0)).ToList()).CopyToDatatable()

Distinct Based on the specific column name from the data table.

DataView view = new DataView(table)
DataTable distinctValues = view.ToTable(true, "Column1", "Column2" ...)
DataTable =DataTable.DefaultView.ToTable(true)

Sorting the data table based on particular column name
dt = dt.Select("",[ColumnName] asc").CopyToDataTable()

Duplicate records from the same data table
(From p in dt.Select() where( From q in dt.Select() where string.Join(",",q.ItemArray).Equals(string.Join(",",p.ItemArray)) Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable()

Duplicate records from the same data table and
If you want specific Column alone mention the column name

(From p in dt.Select() where( From q in dt.Select() where q("ColumnName").Equals(p("ColumnName")) Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable()

Get Max value from the data table column value
int MaxValue=Convert.ToInt32(dt.AsEnumerable().Max(Function(row) row("Column2")))

Join Two Data table
JoinTables.xaml (10.6 KB)


Linq in Data Tables
Duplicate rows in data table
How to remove blank row from dataTable
How to get duplicate rows for specific column in a datatable
How to group repeated email ids present in one column of datatable
Deleteing the whole row if ( condition)
Get distinct value in column excel
Datatable return a row item value if column equals something
Remove duplicates before inserting into database
Remove Empty Rows From DataTable
Filter Datatable - Not like filter
Removing empty rows from excel sheet or datatable
Update datatable
How to Remove Duplicate Columns in Excel and have the distinct values in the other sheet
Compare rows from one excel to other excel
No Sort/Filter Data Table in UIPath
Search for column value and merge data cells
How to find blank rows
Filter error in activities also
How can I find all the values in the duplicate column and merge the duplicate rows?
URgent Can We merge two collection into 1
How to sum all duplicates in a column
Drop datatble
Sort elements in a excel document and save it in a new sheet
Remove duplicate database error
How to Remove Duplicate Columns in Excel and have the distinct values in the other sheet
Merge two datatables with common column
How to create a re-usable component in uipath?
Group by - Data table
How to judge the dataTable's all the cell was blank?
Check first row of the table:if yes allow the whole dt through the loop
Delete empty row from Datatable
How to delete rows in the same sheet (Excel)?
How to filter data table as per the string array
Can't filter the date using the data wizard
Selecting Data from a DT where datas don't exist in another DT
How to use sort datatable activity without sorting the column name of that datatable
Get distinct rows
Variable IEnumerable<Object>
How to delete Empty Rows in Excel
How to remove duplicates in excel on keywords
Compile error when use CopyToDataTable() activity
How to dynamically read a excel range in a for each row loop?
Anchor Base - Bot does not stop
Excel Read Row activity - check if blank/no data returned (ReFramework)
Group data in datatable and count amount of data
Checking Duplicate rows depending on the condition
How to pass WHERE condition in Datatable.defaultview.totable()
How to remove duplicates in an excel
How can I filter a DataTable?
[QUERY] FilterDataTable Activity
How to remove empty cells from datatable?
How to copy exact 50 lines of code from a file when a timestamp is there
Remove data row activity is not working in for each loop
Deleting rows from a sheet after filtering
Filter Table not executing
Excel Automation process
How to check if there are duplicates in the rows of Data Table

Hi arivu96

Really i have got good info from this and it worked also for me.

but i was not able to understand this code completely i am not that much good at dotnet.

could you please give some explanation on this or where can i find stuff something like this.


getting very helpful from this information.

Hi arivu ,

Can you help me with getting Distinct values Based on the specific column index from the data table.


Hi @838486,
First filter the datatable using skip method like below then use the same query

For example:

Arivu :slight_smile:

1 Like

Good info !!!

Hi @arivu96 ,

can you please help me with this?


@arivu96 I have used below expression to select rows in a datatable that contain todays’s date in “Start Date” column

OutDT.AsEnumerable.Where(Function® If(IsDate(r(“Start date”).ToString.Trim), Convert.ToDateTime( r(“Start date”).ToString.Trim ) = TodayDate, False) ).CopyToDataTable

Out of the above filtered datatable I want to select rows which contains “Start Time” more than 2 hours from the current time and copy it to a datatable,Means i need to find the difference between every start time field and current time, if it is more than 2 hours, i need to select those rows…Can you please help me with an expression for that.

Sample data provided below.

JobName Job CreatedBy Status Start date Start Time
Test Job TestBOT Finished 10/8/2019 12:05:53 AM
Test Job TestBOT Finished 10/8/2019 12:05:55 AM
Test Job TestBOT Finished 10/7/2019 3:00:27 AM

Hi @Varshini,
use below code
dt.Select("[Start date] like '"+Now.ToString("M/d/yyyy")+"%'").CopyToDataTable()


@arivu96 Thanks for the expression

Actually i want an expression to filter out “Start Time” column which have time more than 2 hours from the current time and copy it to a datatable,Means i need to find the difference between start time value in each row to the current time, if it is more than 2 hours, i need to select those rows.

The difficulty is that values in a datatable is in string so we need to first convert it to datetime to find the difference in hours…The rows may not contain datetime, so we need to put a check before converting it to datetime.

Thanks in advance,

Hi varshini,

Then try below query and let me know

(From row In dt.Select Where row(“Start date”).ToString=Now.ToString("M/d/yyyy") and Now.Subtract(Convert.ToDateTime(row(“start time”).ToString)).TotalMinutes>120 Select row).CopyToDataTable()




It did worked, Thanks so much for your help!!

Best Regards,


I have a spreadsheet that has many columns with headers showing goods that have been booked in.

Currently i read the whole range of the spreadsheet. Then in a try catch i assign a DataRow[] variable with the value:

DT.Select(“PO_NO=” + po)

(Where PO_NO is the Column Header & po is a variable created earlier that holds the PO number that i’m looking for in all the data on the spreadsheet)

This works as it is, but i’d like to be able to expand the value in the assign activity to include another column header and variable to increase the accuracy of the search. I thought it would be something like:

DT.Select(“PO_NO=” + po) and (“DNote=” + dn)

However this is clearly not working, do i need to repeat the DT.Select proportion or maybe use “and also” rather than just the “and”??? Or maybe i need to filter once by the PO save those results somewhere then filter again by the Delivery Note???

Or am i completely off the mark???

Also is there a way of either saving the filtered rows from the initial spreadsheet once the 2 criteria have been met, into a new spreadsheet or new DT…? As i am going to need to append this once i have the correct data.

Any help would be greatly appreciated
Thanks in advance

hi @arivu96

How to determine whether the table only has empty row or not? (not using for each row as it will be better to do it on one go)

I have a data table with 2 columns ,I need to remove duplicates from first one and get the max value from second one

Hi @m.abdullatif

Use remove duplicate row and based on int MaxValue=Convert.ToInt32(dt.AsEnumerable().Max(Function(row) row(“Column2”)))

ashwin S

hey i want remove duplicate row based on keywords or particular string value
i’ve to retain the first one then remove the rest

column1 | column2 | column3
abc | abc | 123
123 | abc | 456
123 | abc | 456
abc | abc | 123
abc | abc | 123
abc | abc | 123

so my output should be
column1 | column2 | column3
abc | abc | 123
123 | abc | 456
123 | abc | 456

Hi @Pradeep_Shiv

and then store the values in Dt

and then print the values

Ashwin S

column1 | column2 | column3

123 | abc | 456
123 | abc | 456
abc | abc | 123
abc | abc | 123
abc | abc | 123

First row is removed ,i have to keep the first occurance then delete the rest

I am using the below query to convert one column of my datatable to string array :

String array = (dt2.AsEnumerable().Select(Function(a) a(5).ToString)).ToArray

I need to convert mulitple columns (3 columns from the datatable into string array) using the above query, Please help