Table of Contents
MODIFY COLUMN(S)
Category: Transform / Basic
Description
This action calculates and replaces the values in one or more existing columns using expression(s). Each expression replaces one column.
Use cases
- Apply standardized abbreviations to full-length text values (or full-length values to abbreviations).
- Removing prefixes or suffixes from text values.
- Convert formatting of imported values to align with a required destination schema.
- Correct common misspellings.
- Update outdated values.
Action settings
| Setting | Description |
|---|---|
| Column to replace | Select the name of the column to replace. |
| Expression or Value | Options: It's an expression or It's a text or number. Enter the expression used to calculate the updated column values, or, enter the text or numeric value that will be repeated for all values. |
Click Replace more columns to modify additional columns within this same action.
Remarks
You can refer to the same column in expressions. In this case, the original value from this column will be used.
Examples
Example #1
Replace students' final numeric grade with the equivalent letter grade.
Before (source table)
| Student | Class | Final grade |
|---|---|---|
| Enrique | Math | 85 |
| Sally | Language | 72 |
| Juliette | Math | 99 |
| Rick | Science | 68 |
After (result table)
| Student | Class | Final grade |
|---|---|---|
| Enrique | Math | B |
| Sally | Language | C |
| Juliette | Math | A |
| Rick | Science | D |
Action parameters
Column to replace: Final grade
Expression: if [Final grade] >= 90 then "A" else if [Final grade] >= 80 then "B" else if [Final grade] >= 70 then "C" else if [Final grade] >= 60 then "D" else "F"
Example #2
Calculate the dollar amount of compulsory deductions for each country, replacing the percent values with the dollar amounts.
Before (source table)
| Country | Gross income | Disposable income | Compulsory deductions |
|---|---|---|---|
| United States | 56067 | 45582 | 18.7% |
| Australia | 51050 | 42617 | 16.52% |
| Switzerland | 53716 | 48414 | 9.87% |
| Canada | 45896 | 37469 | 18.36% |
After (result table)
| Country | Gross income | Disposable income | Compulsory deductions |
|---|---|---|---|
| United States | 56067 | 45582 | 10485 |
| Australia | 51050 | 42617 | 8433 |
| Switzerland | 53716 | 48414 | 5302 |
| Canada | 45896 | 37469 | 8427 |
Action parameters
Column to replace: Compulsory deductions
Expression: ([Gross income] - [Disposable income])
Community examples
- Optional calculations with columns (Project; Module: Main; Group: Tab 1; Table: Optional calculation; Action position: 3)
- How to obtain a list of all files recursively (Project; Module: Main; Group: Tab 1; Table: Table 1; Action position: 3)
- Match action - matching multiple values (Project; Module: Get matches; Group: Tab 1; Table: Lookup table; Action position: 4)
- Transforming Single Row of Data from CSV into Multiple Rows (Project; Module: Main; Group: Tab 1; Table: Table 1; Action position: 3)
- Reverse Fill-down (Project; Module: Main; Group: Group 1; Table: Table 2 (2); Action position: 3)
- Parsing Exceptionally Complex Column (Project; Module: Main; Group: Group 1; Table: Table 1 (parsed); Action position: 3)
Shortcuts
Fast ways to create the action:
- Right-click on a column heading, and select "Modify", then "With expression…" from the submenu.
- Click the drop-down arrow in a column heading and select "Modify", then "With expression…" from the submenu.