Data Cleansing Tool
Data Cleansing Tool
One Tool Example
Data Cleansing has a One Tool Example. Visit Sample Workflows to learn how to access this and many other examples directly in Alteryx Designer.
Use Data Cleansing to fix common data quality issues. You can replace null values, remove punctuation, modify capitalization, and more!
Known Limitations
The Data Cleansing tool is not dynamic. If used in a dynamic setting, for example, 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.
Visit the Alteryx Community Tool Mastery series to learn even more about this and other tools.
Tool Components
The Data Cleansing tool has 2 anchors.
Input anchor: Use the input anchor to connect the data you want to cleanse.
Outputanchor: The output anchor outputs the cleansed data.
Configure the Tool
Use the Options tab to determine how data quality issues are managed.
Remove Null Data
Use these options to remove entire rows and columns of null data.
Remove Null Rows
Remove all rows with a null value in every column.
Remove rows with null values—doesn't remove rows with empty string values.
Only remove rows that have a null value in every column.
A message displays in the Results window with the number of rows that were removed.
Remove Null Columns
Remove all columns with a null value in every row.
Remove columns with null values—doesn't remove columns with empty string values.
Only remove columns that have a null value in every row.
A message displays in the Results window with the number of columns that were removed
Select Fields to Cleanse
Select the fields to cleanse with the configuration options below. Use the All link to select all fields and use the None link to deselect all fields.
String Data Types
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): Replace null values with a blank string value. A blank registers as " " rather than [Null]. This option is selected by default.
Replace with 0 (Numeric Fields): Replace null values with a 0 (zero). This option is selected by default.
Remove Unwanted Characters
Leading and Trailing Whitespace: Remove leading and trailing whitespace. This option is selected by default.
Tabs, Line Breaks, and Duplicate Whitespace: Replace any occurrence of whitespace with a single space, including line endings, tabs, multiple spaces, and other consecutive whitespaces.
All Whitespace: Remove any occurrence of whitespace.
Letters: Remove all letters, including non-Latin alphabet letters like A b Z À é ö.
Numbers: Remove all numbers.
Punctuation: Remove these characters:
! " # $ % & ' ( ) * + , \ - . / : ; < = > ? @ [ / ] ^ _ ` { | } ~
Modify Case
Select Modify Case and then choose an option from the dropdown to change the capitalization of string data types:
Upper Case: Capitalize all letters in a string.
Lower Case: Convert all letters in a string to lowercase.
Title Case: Capitalize the 1st letter of all words in a string.