Version

    Replace empty values

    Replace empty values allows you to replace _No value_s or empty strings with a constant value. The step is useful, for example, to provide default values when the input does not provide a value.

    Parameters

    • Input column: required, a column of any type in which you’d like replacements to be made.

    • Replacement: required, value to use when empty string or No value is encountered in specified input column. The data type and formatting of the replacement value depends on the data type of the input column:

      • boolean: use either true or false.

      • integer: use numbers without any grouping, e.g., use 123456 instead of 123 456.

      • decimal: write decimal values without any grouping and with period as decimal character, e.g., 123456.789 instead of 123,456.789.

      • string: write a string without enclosing quotes, e.g. replacement text.

      • date: write date or full date and time using format yyyy-MM-dd HH:mm:ss.SSS, e.g., 2023-04-04, 2023-04-04 17:30, 2023-04-04 17:30:05.022.

    • Target column: required, configure the column which will receive the output. Data type of the output colum will remain without change.

      • Write result to the current column: overwrite the input column with the result.

      • Create new column with name: create a new column with specified name. Name of the new column can contain spaces or special characters - technical column name will be created automatically. The new column will be placed right after the input column. ne)

    Examples
    String values

    In this example we read data from Color column and write the result into Color 2 column. Notice that the empty string in the second row was also replaced with n/a.

    replace empty string
    Date values

    When specifying the date replacement, the formatting of the Replacement parameter is always yyyy-MM-dd HH:mm:ss.SSS regardless of the formatting of the input column. On the following screenshot we show two replacements:

    • Transaction datetime column uses replacement 2023-05-01 without the time part. Midnight on the given date is used. The result is in Transaction time 2 column.

    • Due date uses replacement 2023-12-24 even though the column formatting is configured as d MMMM yyyy. The result is in Due date 2 column.

    replace empty date
    Integer values

    We read from Seats column and write the output into Seats 2 column.

    replace empty integer
    Decimal values

    We read from Paid amount column and write the output into Paid amount 2 column.

    replace empty decimal
    Boolean values

    Replacement written as false is used. Note that you cannot use yes or no etc. in the replacement value, only true and false are allowed.

    replace empty boolean
    See also