URGENT: Filter datatable

Help
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

1 Like
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.

14

Hi Abdel,

How did you manage to resolve the #NA Issue. I am also getting the same problem. Can you please share your solution .

Thanks in Advance.

15

Hi @arivu96
I copy your code to use assign activity. However, the error code occurred, “Assign: The source contains no DataRows.”
Could you help me to solve it? Thank you sooo much.

16

Can you suggest me if I want to unselect 2 values from column A and with that select one value from column B. Then need to remove data rows.

17

@abdel

Can you please share the solution what you followed, I am also stuck in such issue !

Thanks
Nikhil

18

@pari_patel, can you try filter datatable

image
image920×484 30.9 KB

19

What worked For me is whenever I read the excel in datatable all #N/As were coming as numeric value so I use filter datatable activity to keep only rows with that numeric value and while writing data to some other sheet, I looped through each current row in new datatable for that numeric value and updating then back to #N/As using Assign activity in For each loop in datatable activity.