User Tools

Site Tools


transformations:replacecolumns

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

SettingDescription
Column to replaceSelect the name of the column to replace.
Expression or ValueOptions: 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


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.


See also

transformations/replacecolumns.txt · Last modified: 2025/01/14 15:48 by craigt

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki