Tutorials & Examples Web-help Blog

Replace empty cell as text with real empty value


#1

Sometimes, I run into the situation in which I have an empty cell that has “text” as data type when I look in the meta data of that cell.

Is there a way to transform this empty cell to a real empty value (empty()) ?
How should I do this properly using the table-wide replace transformation ?

Thanks in advance !


#2

You can use the “Modify column(s)” transformation with this expression:

[ColumnA] = when(len([ColumnA]) > 0, [ColumnA])

which is a shorthand equivalent of

[ColumnA] = if(len([ColumnA]) > 0, [ColumnA], empty())

Replacing empty text with real empty values not possible with the Table-wide Replace transformation.


#3

Hi Dmitry,

I have noticed that when I transform a column containing text values to numbers with a data type transformation, that the blank cells are not converted to empty() values.
Is this the normal behavior ? Is there a workaround for replacing these for the entire dataset or do I have to do the transformation you proposed for each column individually ?

Thanks in advance !


#4

Hi Nikolaas,

this is expected behavior as converting text to numbers doesn’t necessarily mean that empty text values should be converted to nulls. At this point the only option is to use the expressions I suggested above.


#5

Ok thanks for the response !


#6

Is it possible to add the functionality to replace empty strings to real empty values for an entire dataset ?
Sometimes we need to convert a lot of text fields to uppercase en this results in an empty string for the converted columns. It would be nice if we could set them as empty values all at once.


#7

In version 3.9.3 planned for release in about 1 week it will be possible to replace empty values (i.e. empty or empty text) with a text constant (e.g. “NULL”) for entire dataset using the new “Empty to text or number” command of the “Convert data type” action. If you then apply the “Table-wide replace” action to remove “NULL” from all columns you will receive real empty values.