Table of Contents
TABLE-WIDE REPLACE
Category: Transform / Advanced
Description
This action performs the same replacement in all or selected columns.
Use cases
- Use this action to correct dataset-wide misspellings.
- Expand abbreviations into full text, or abbreviate full text values.
- Standardize formatting throughout records.
- This action is typically used for data cleansing.
- 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 #1
Update employee records (email, company name) after "Old Inc." was rebranded to "New Inc."
Before (source table)
| Name | Company | |
|---|---|---|
| Alice | Alice@Old.com | Old Inc. |
| Bob | Bob@Old.com | Old Inc. |
| Carol | Carol@Old.com | Old Inc. |
After (result table)
| Name | Company | |
|---|---|---|
| Alice | Alice@New.com | New Inc. |
| Bob | Bob@New.com | New Inc. |
| Carol | Carol@New.com | New Inc. |
Action parameters
Search for: Old
where it's: Entire cell or part of longer text
Replace with: New
which is: Matching part
Perform replacement in: All columns
Example #2
Standardize the Finance Department's name throughout employee records.
Before (source table)
| Emp ID | Department | Salary ($) |
|---|---|---|
| 101 | Fin. Dept. | 70,000 |
| 102 | Finance Dept | 72,000 |
| 103 | Fin. Dept. | 68,000 |
After (result table)
| Emp ID | Department | Salary ($) |
|---|---|---|
| 101 | Finance Dept | 70,000 |
| 102 | Finance Dept | 72,000 |
| 103 | Finance Dept | 68,000 |
Action parameters
Search for: Fin. Dept.
where it's: Entire cell
Replace with: Finance Dept
which is: Entire cell
Perform replacement in: Selected columns
Selected columns: Department