Data Cleansing Tool Icon

Data Cleansing Tool

Version:
Current
Last modified: November 25, 2019

Use the Data Cleansing tool to fix common data quality issues.

Known limitations

The Data Cleansing tool is not dynamic. If used in a dynamic settings, such as a macro intended to work with newly generated field names, the tool will not interact with the fields, even if All options are selected. Consider replacing the Data Cleansing tool with a Multi-Field Formula Tool.

Numbers with more than 15 digits need to be treated as strings, or they lose precision. Set the field type to a string using the Select Tool.

Visit the Alteryx Community Tool Mastery series to learn even more about this and other tools.

Configure the tool

Use the following fields to determine how data quality issues are managed.

Select Fields to Cleanse

Select the fields to cleanse using the configuration options below. All options, except for Replace Nulls with 0, apply to string data types. To specify different options for different fields, use multiple Data Cleansing tools in your workflow.

Replace Nulls

To replace nulls with values other than blanks or 0, use the Imputation Tool.

  • Replace with Blanks (String Fields): A Blank registers as "" rather than [Null]. Selected by default
  • Replace with 0 (Numeric Fields): Selected by default.

Remove Unwanted Characters

  • Leading and Trailing Whitespace is selected by default.
  • Tabs, Line Breaks, and Duplicate Whitespace replaces any occurrence of whitespace with a single space, including line endings, tabs, multiple spaces, and other consecutive whitespace.
  • All Whitespace removes any occurence of whitespace.
  • Letters removes all letters. This includes letters outside of the Latin alphabet.
    A b Z À é ö
  • Numbers removes all numbers.
  • Punctuation removes the following characters:
    ! " # $ % & ' ( ) * + , \ - . / : ; < = > ? @ [ / ] ^ _ ` { | } ~

Modify Case

Select an option to change the capitalization of string data types:

  • Upper Case capitalizes all letters in a string.
  • Lower Case converts all letters in a string to lowercase.
  • Title Case capitalizes the first letter of all words in a string.
Was This Helpful?

Need something else? Visit the Alteryx Community or contact support.