URGENT: Filter datatable

studio

#1

hello,

i had a datable that in one column there is a special caracter “#N/A” , i would to keep only the row columns that match this special caracter:

image

i'm trying to use select fonction

#2

Hi @abdel
datatable.AsEnumerable().Where(Function(row) row(“column”).ToString = “#N/A”).CopyToDataTable()

Regards
Balamurugan


#3

Hi balupad14,

it does not working:
here is the code Main.xaml (39.6 KB)


#4

DataRow[] results = table.Select("ColumnName= ‘#N/A’ ");
Datatable DT=results.Copytodatatable();
@abdel Try this


#5

i had tryed this but it generate an error


#6

Hi @abdel,

table=table.Select("Convert(ColumnName,System.String)= '#N/A'").CopyToDataTable()

Regards,
Arivu


#7

hi @abdel

Have you tried using this post

Thanks
Ashwin S


#8

Hi @abdel,
Can you try this?


#9

image


#10

Hello Abdel, I hope you found the solution if not then, try replacing the #Na with Na. if you are reading data from an Excel then the #Na value get’s replaced by a numeric string when read into a data table, and because of that the filter data table doesn’t work.

To overcome this you can use IFERROR function in excel along with the required function that is populating the result in the desired column.

From there on, you can create a new table which should be a clone of the previous table (this will make sure the schema of both of the tables is same), and then filter the original Data table using for each row activity and add the required row to the new data table by add data row activity.
Refer the attached Excel and main XML .

Thank you.
Filter NA values.zip (10.0 KB)


#11

@abdel,
#N/A means not able to drive or formulat error etc in excel perspective. I suggest to write this complete data table in csv/excel. Check what was the value came in the csv/excel for the #N/A column. You use that value to filter the data table.

Regards,
Balram.


#12

Thanks,

i resolved the problem,

there is a question i had to do a condition if: row(“Column”).ToString <> {"[Val1]" OR “[Val2]” OR “[Val3]” OR “[Val4]” }

is it possible ?


#13

Hello @abdel,
Share the solution how you resolved for the community benefits.
there is a question i had to do a condition if: row(“Column”).ToString <> {"[Val1]" OR “[Val2]” OR “[Val3]” OR “[Val4]” }
If you are using foreach data row activity, you can use if condition to check the specific column values. for eg item is the row and to check the value of the column just use item(“Name”).tostring =“John”. If you are not read the headers use index of the column to check the value.