Data Cleansing Tool
The Data Cleansing tool fixes common data quality issues using a variety of parameters.
Known limitation: dynamic workflows
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.
Known limitation: large number handling
Numbers with more than 15 digits need to be treated as strings, or they lose their 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.
Remove Null Data
Use these options to remove entire rows and columns of null data.
Remove Null Rows
- Removes all rows with a null value in every column.
- Removes rows with null values—does not remove rows with empty string values.
- Only removes 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
- Removes all columns with a null value in every row.
- Removes columns with null values—does not remove columns with empty string values.
- Only removes 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 Columns to Cleanse
Select the columns (fields) you want to cleanse. Select from the following options:
Replace Nulls
- Replace with Blanks (String Columns): A Blank registers as "" rather than [Null]. Selected by default
- Replace with 0 (Numeric Columns): 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.