How can I change NA values with Null?

[Aknefe_012]

Hi.I want to change multiple NA values in columns to be Null.How can I achieve this?

[ArjenEX]

Hi @Aknefe_012

If the text is exactly just “NA”, you can use toNull(replace($column1$, "NA", "")) in a String Manipulator.

[Aknefe_012]

It didn’t helped,I tried it under ‘wdi_prop_less_2_usd_day’ and didn’t worked,any other suggestion

[ArjenEX]

Then your values are not clean. This is an alternative with CE:

if (contains(column("column1"),"NA") == true ) {
    null
} else {
    column("column1")
}

[takbb]

Hi @Aknefe_012 , when you used @ArjenEX 's String Manipulation code, did you have it set to append a new column or replace the existing column?

As mentioned, that code should work if your columns contain “NA”. The problem with it though is that it might work too well as it would also remove NA from any string that contains it,

e.g.

Original	Replacement
NA	?
NAME	ME
DNA	D
ENAMEL	EMEL

So I wonder if you took the above screenshot literally and Appended the “output” column, instead of replacing the original column. We can’t tell from your screenshot.

If you have multiple columns, String Manipulation (Multi Column) might be a good choice, to save having to code it for every column.

string(strip($$CURRENTCOLUMN$$).equals("NA")?toNull(""):$$CURRENTCOLUMN$$)

or if your “NA” strings don’t contain whitespace then simply:

string($$CURRENTCOLUMN$$.equals("NA")?toNull(""):$$CURRENTCOLUMN$$)

This translates to
“return a string such that: if currentcolumns=“NA” then return null, otherwise return currentcolumn changed”

An alternative to that, without the fancy conditionals is to use regexReplace:

toNull(regexReplace($$CURRENTCOLUMN$$,"^NA$",""))

which will replace only strings that are exactly “NA”

Make sure to select all the required columns, and have it “Replace selected input columns”

If you are still having problems, I would suggest you upload a sample workflow so we can see what is happening.

[Aknefe_012]

Thanks for answering.