Transformation: Replace

The Replace transformation in Nexadata Pipelines allows you to find and replace values within a specific column, with options for exact matching or using regular expressions for advanced pattern matching. This transformation is useful for correcting data, standardizing text, or updating values to align with a specific format or nomenclature. You can configure the transformation using Natural Language Mode or Advanced Mode, depending on the level of control required.

Replace also accepts optional Conditions. Without conditions, every matching row in the column is replaced. With conditions, the replacement applies only to the rows that satisfy them, and every other row passes through untouched.

Inputs for the Replace Transformation

Name of the Transformation: In Natural Language Mode, the transformation name is automatically generated, but you can update it later in Advanced Mode. For example, you might rename it to “Standardize Location Names” or “Replace Outdated Titles”.
Column to Perform Find and Replace: Choose the column where the find and replace operation will be applied. For example, you may select “Department_Name”, “Location”, or “Status”.
Replace Type: Choose the type of replace operation:
- Exact match (all matches replaced): Finds values that exactly match the specified find value.
- Regular Expression: Uses a regular expression to identify patterns in the data for more flexible find-and-replace operations.
Value to Find: Enter the value or pattern you want to locate within the selected column. This could be a specific word, phrase, or pattern if using regular expressions.
Replacement Value: Enter the value that will replace each match found in the selected column. This could be a standard name, updated text, or a reformatted string.
Conditions (optional): Restrict the replacement to a subset of rows based on what other columns contain. Without conditions, every row is replaced. See Scoping a Replacement with Conditions below.

Using Natural Language Mode

In Natural Language Mode, describe the replacement you want to perform, and Nexadata will automatically configure the transformation. The transformation name is auto-generated but can be modified later in Advanced Mode.

Example Instructions in Natural Language Mode

Replace all instances of “NY” in Location column with “New York.”
Find and replace “Inactive” with “Former” in the Status column.
Replace abbreviations in Department_Name with full names using regular expressions.
Replace “Temp” with “Temporary” in Job_Title.
In Employee_Status, replace “Term” with “Terminated.”
Where Amount Type is Credit, replace the value in Segment 2 with the value in Cost Center.
Replace “Pending” with “On Hold” in Status, but only where Region is EMEA.

Using Advanced Mode

In Advanced Mode, you have complete control over the Replace transformation, allowing you to manually specify the column, replacement type, find value, and replace value. Advanced Mode allows for detailed control, ensuring the transformation precisely aligns with your analytical needs.

Steps in Advanced Mode

Name of the Transformation: Enter or update a custom name, such as “Update Location Names” or “Replace Temporary Titles”.
Column to Perform Find and Replace: Select the column where you want the find-and-replace operation, such as “Location” or “Status”.
Replace Type: Choose the type of replace operation:
- Exact match (all matches replaced) for locating specific values.
- Regular Expression for pattern-based replacements.
Value to Find: Specify the text or pattern to find within the selected column.
Replacement Value: Enter the new text that will replace each match found in the column.
Conditions: Leave this empty to replace every matching row, or add rules to scope the replacement. See the section below.

Example Use Case: Standardizing Location Names

The Replace transformation is ideal for refining data by standardizing terms. For example, suppose your Location column contains abbreviations like “NY” and “LA”, which you want to replace with full city names. For example:

Transformation Name: Standardize Location Names
Column to Perform Find and Replace: Location
Replace Type: Exact match (all matches replaced)
Value to Find: NY
Replacement Value: New York

This configuration will replace each instance of “NY” in the Location column with “New York”, improving consistency in the dataset.

Examples of Find and Replace Instructions

Simple Exact Match
- Description: Replace abbreviations in a column with full names.
- Instruction: Replace “Mgr” with “Manager” in the Job_Title column.
Exact Match with Special Characters
- Description: Replace numeric codes with text labels.
- Instruction: Replace “1234” with “North Region” in the Region_Code column.
Regex Example with Capturing Groups
- Description: Standardize names that may have middle initials.
- Instruction: Replace names in the Full_Name column in the format (First M. Last) with First Last by removing the middle initial.
- Regex: Find: (\w+)\s\w\.\s(\w+) Replace: $1 $2
Regex for Multiple Word Replacement (Greedy)
- Description: Find and replace the longest substring between two words.
- Instruction: Replace everything between “Begin” and “End” with “Section Content” in the Notes column.
- Regex: Find: Begin.*End (Greedy) Replace: Section Content
Non-Greedy Regex with Optional Matches
- Description: Capture the shortest substring between two markers.
- Instruction: Replace text between “Start” and “Finish” with “Processed” in Comments.
- Regex: Find: Start.*?Finish (Non-Greedy) Replace: Processed
Using Parentheses to Capture and Rearrange
- Description: Reformat dates from MM-DD-YYYY to YYYY-MM-DD in the Date_Recorded column.
- Instruction: Use regex to capture and rearrange date components.
- Regex: Find: (\d{2})-(\d{2})-(\d{4}) Replace: $3-$1-$2
Complex Regex with Group Replacement
- Description: Extract initials from names and place them in parentheses.
- Instruction: Replace names in Employee_Name column in First Last format with First (L.).
- Regex: Find: (\w+)\s(\w)\w+ Replace: $1 ($2.)
Partial Word Replacement with Regex
- Description: Standardize department names containing “Fin” as “Finance”.
- Instruction: Replace “Fin” with “Finance” in the Department column.
- Regex: Find: \bFin\b Replace: Finance
Pattern Matching with Non-Greedy Tag Extraction
- Description: Remove any HTML tags, preserving only the inner text.
- Instruction: Remove HTML tags in Description column.
- Regex: Find: <.*?> (Non-Greedy) Replace: “ (empty string)
Capturing Repeating Patterns
- Description: Standardize address abbreviations by capturing specific terms.
- Instruction: Replace addresses in the format 123 Main St. to 123 Main Street in Address.
- Regex: Find: (\bSt\.) Replace: Street

Scoping a Replacement with Conditions

By default, Replace applies to every row in the column that matches your Value to Find. Conditions narrow that down, so the replacement runs only on rows where other columns satisfy the rules you set.

Conditions use the same structure as the Filter transformation and conditional Flip Sign, so if you have built either of those, this will look familiar.

Building a Condition Set

The Conditions block starts empty, with the note “Optional. Without conditions, every row is replaced.” Add rules to scope it:

And / Or: Choose how the rules combine. And requires every rule to pass. Or requires any one of them.
+ Rule: Add a single condition.
+ Group: Add a nested set of rules with its own And/Or logic, for conditions that cannot be expressed as one flat list.

Each rule has four parts:

Column: The column to evaluate. This does not have to be the column you are replacing in.
Operator: How to compare, for example Equal.
Comparison: What to compare against. Choose Value for a literal, Column to compare against another column on the same row, or Percentage.
Value: The literal, column, or percentage the operator tests against.

Use the trash icon at the end of a rule to remove it.

Referencing Other Columns

Both the conditions and the Replacement Value support the standard $ColumnName convention. This means a replacement can be sourced from another column on the same row, rather than being a fixed literal.

For example, setting the Replacement Value to $Cost_Center writes each row’s own Cost Center value into the target column.

Example: Conditional Substitution

Replace an event ID, but only on one specific audit record:

Column to Perform Find and Replace: EVENT_ID
Replace Type: Exact match (all matches replaced)
Value to Find: USR-8
Replacement Value: USR-8X
Conditions: AUDIT_ID Equal Value 736963573

Every row where AUDIT_ID is 736963573 and EVENT_ID is USR-8 becomes USR-8X. Every other row, including other rows containing USR-8, passes through unchanged.

Example: Sourcing the Replacement from Another Column

A common EPM and GL shaping pattern:

Where Amount Type is Credit, replace the value in Segment 2 with the value in Cost Center.

Column to Perform Find and Replace: Segment 2
Replacement Value: $Cost_Center
Conditions: Amount_Type Equal Value Credit

Before conditions existed, this took either a set of filtered supporting pipelines stacked back together, or a Mapping Group that could not evaluate column state the same way. It is now a single transformation step.

Summary

The Replace transformation in Nexadata Pipelines provides a powerful tool for find-and-replace operations, enabling you to update values within a column based on exact matches or regular expression patterns. Add Conditions to scope the replacement to specific rows, or leave them off to replace every match in the column. Use Natural Language Mode for a quick setup or Advanced Mode for detailed control. This transformation is ideal for standardizing, correcting, or reformatting data within specific columns.