Replace empty cell as text with real empty value

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 !

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.

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 !

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.

Ok thanks for the response !

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.

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.

Hi Dmitry,

Sometimes I see that when I import data from excel, there are “empty” values but there’s not real empty values. When I look at their metadata, they are of length zero.

How is this possible ? When I try to generate a testfile in Excel and I import it, it is always a real empty value. Any idea how this can occur ?

Hi Nikolaas,

Those are empty strings. You can make one by typing in ="" or just ' to a worksheet cell. Also, some Excel formulas may return an empty string.

Ok thanks

I am looking for something that could help me to move empty cells and shift values to the left.

Could you please give some ideas?
Thank you!

Hi @Dinho

I think I solved it for you - This is the best way I could find, without having to know the exact empty cells. The other option I played with was the ‘Shift column up/down’ trransformation, but this required me to know which cells were empty. (7.4 KB)

Input/output as follows

  1. Import the file
  2. Unpivot all columns except Column 1
  3. Remove empty rows from column Data
  4. If column ‘Labels’ ends with ‘1’, rename to ‘Label’, else leave empty
  5. Pivot based on column Data both as column labels and data (type Any)
  6. Sort column 1 in ascending order
  7. Remove column Labels

@DKcrm Thank you very much! This is awesome!