Table of Contents
CLEAN UP
Category: Transform / Basic
Description
This action removes specified values, or values of a particular type (e.g. text), from one or more columns by replacing them with empty cells.
Use cases
Clean Up is used for dealing with data quality issues - e.g. removing text values from columns where only numbers are expected, or removing specific undesired values. Most typically, Clean Up is used when importing data from Excel spreadsheets.
Action settings
| Setting | Description |
|---|---|
| Column | Click the down arrow to choose which columns Clean Up will affect. Options: Column (and select a specific column), All columns (to clean the entire dataset), or Selected columns (and select which columns will be cleaned). |
| Remove… | Select the types of values, or specific values, to be removed. See below. |
Remove options
| Option | Description |
|---|---|
| Remove numbers | All numeric values are removed. |
| Remove everything but numbers | All numbers are kept; non-numeric values are removed. |
| Remove text values | All text values are removed. |
| Remove everything but text values | Text values are kept; non-text values are removed. |
| Remove whitespace only values | Removes values containing only spaces. |
| Remove errors | All error results are removed. |
| Remove particular values | Options: Remove selected values or Remove all but selected. When selected, a list of all unique values in the column is displayed for the user to manually select which values to keep or remove. |
Remarks
Unlike Filter, this transformation doesn't remove table rows.
Community examples
- Example: Getting public holidays from an API with Web Request (Project; Module: Main; Group: Tab 1; Table: Public holidays;
Action position: 4) - Duplicating rows based on a condition (Project; Module: Main; Group: Tab 1; Table: Table 1 (2); Action position: 5)
Shortcuts
Fast ways to create the action:
- Right-click a value and select "Clean Up". Choose whether to "Keep only [value]" or "Remove [value]" from the submenu.