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.
Examples
Example #1
Clean non-numeric data from Sales column.
Before (source table)
| Salesperson | Sales |
|---|---|
| Sveta | 11230 |
| Rick | Kiev |
| Dan | 8290 |
| Yelena | #Error |
After (result table)
| Salesperson | Sales |
|---|---|
| Sveta | 11230 |
| Rick | |
| Dan | 8290 |
| Yelena |
Action parameters:
Columns to clean: Sales
Option: Remove everything but numbers
Example #2
Clean improper data from the Department column.
Before (source table)
| Department | Headcount |
|---|---|
| Admin | 4 |
| 15 | 15 |
| HR | 3 |
| *TBD* | 0 |
| Sales | 4 |
After (result table)
| Department | Headcount |
|---|---|
| Admin | 4 |
| 15 | |
| HR | 3 |
| 0 | |
| Sales | 4 |
Action parameters:
Columns to clean: Department
Option: Remove everything but text values
Option: Remove particular values ("TBD*)
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.