Table of Contents
TABLE-WIDE REPLACE
Category: Transform / Advanced
Description
This action performs the same replacement in all or selected columns.
Use cases
This action is typically used for data cleansing, e.g. for removing the word 'NULL' in all columns.
Cleaning up the "(not set)" values that Google Analytics uses to represent blank cells.
Action settings
| Setting | Description |
|---|---|
| Search for | Enter the value to search for. |
| where it's | Select whether to match whole-cell values, or whole-cell values and partial values. Options: Entire cell only or Entire cell or part of longer text. |
| Replace with | Enter the value to replace the search value with. |
| which is | Select whether the replacement value will occupy the entire cell or just the matching portion of the found search value. Options: Entire cell or Matching part. |
| Perform replacement in | Select whether to search all or specific columns. Options: All columns or Selected columns (and select the column(s) to search from the list). |
Remarks
This action is case-sensitive. If the Search for value does not match values case-wise, the replacement is not made.
Numeric values can only be searched for, and replaced as, entire cell values. Upon entering a numeric value in the Search for field, only the Entire cell only options will be selectable.
Examples
Example: Clean out NULL values from the source table, replacing them with blank cells.
Source table: The longest rivers in the world
| River | Length (km) | Continent |
|---|---|---|
| Nile | 6650 | Africa |
| NULL | 6400 | South America |
| Yangtze | 6300 | NULL |
| Mississippi | NULL | North America |
| Yenisei | 5539 | Asia |
| Yellow River | 5464 | NULL |
| Ob | NULL | NULL |
| Paraná | 4880 | South America |
Action parameters:
Search for: "NULL"
where it's "Entire cell only"
Replace with (leave blank)
which is "Entire cell"
Perform replacement in "All columns"
Result table:
| River | Length (km) | Continent |
|---|---|---|
| Nile | 6650 | Africa |
| 6400 | South America | |
| Yangtze | 6300 | |
| Mississippi | North America | |
| Yenisei | 5539 | Asia |
| Yellow River | 5464 | |
| Ob | ||
| Paraná | 4880 | South America |