How to filter excel data table which contains #N/A values

[Zeki_Berk_Gokkaya]

Hello everyone,
I have been trying to filter “#N/A” values using filter data table activity. I’ve tried “Column” = “#N/A” and “Column” Contains “#N/A” as shown below. All I can get is empty data table. Can anyone please help me?

Thanks in advance!

[Karuna (Karuna Devanagavi)]

@Zeki_Berk_Gokkaya
The value “#N/A” is considered as empty value while reading the datatable so try as below

[AMRITANShlal]

Hey @Zeki_Berk_Gokkaya

Can you please provide a sample of DataTable?

[Zeki_Berk_Gokkaya]

Consider #YOK values as #N/A

There will be much more data later on

[Zeki_Berk_Gokkaya]

It does not work, unfortunately

[Karuna (Karuna Devanagavi)]

@Zeki_Berk_Gokkaya
Try this
FilterDatatable.zip (8.6 KB)

[Zeki_Berk_Gokkaya]

This is a bit different from my excel file.
I have cells with formulas that returns #N/A values

[AMRITANShlal]

Hey @Zeki_Berk_Gokkaya

When you’re using ‘Read Range’ activity, make sure to enable PRESERVE FORMAT checkbox in properties panel. Then you could use aforementioned snap in your question.

And it’ll remain the same while filtering datatable. You can use “KONTROL”=“#N/A”

[Karuna (Karuna Devanagavi)]

@Zeki_Berk_Gokkaya
Could you please share your excel?

[mz3bel (Mohssine HILAL)]

Hi @Zeki_Berk_Gokkaya

Welcome to the UiPath community.

So, apparently 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.

Follow below link, it should help you fix your issue

URGENT: Filter datatable

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…

Cheers,
Mohssine

[Zeki_Berk_Gokkaya]

Veri Takip 2021.xlsx (2.9 MB)

Could you please check the “Ana Tablo” worksheet?

[ceyda_eren]

Hi,
After reading Excel, the NA values ​​on the control column come as less than zero, so you can filter as less than zero in the filter data table.It works this way

[Karuna (Karuna Devanagavi)]

@Zeki_Berk_Gokkaya
Its working at my end. Here you go
FilterDT.zip (1.6 MB)

Run the sample program and Please check the sheet1 in Veri Takip 2021.xlsx|

[Zeki_Berk_Gokkaya]

That is completely what I was looking for. The easiest way. Thanks Ceyda!