Use Find and Replace

Use the Find and Replace transformation to search for values within one or more columns and replace them with new values. Use this transformation to standardize dimension members, simplify reporting categories, and clean source data before loading it into a planning sheet or report.

The following examples demonstrate how to use Find and Replace on the Market & Geography column.

Replace a text value#

In this example, the Market & Geography column contains values such as Channel Partners,Canada, Enterprise,Canada, and Government,Canada.

To replace all occurrences of Canada with CA:

1. On the Transform tab, select Find and Replace.
2. In Target Columns, select Market & Geography.
3. In Find, enter Canada.
4. Select Fuzzy Match.
5. In Replace With, enter CA.
6. Select Apply.

After you apply the transformation, the Market & Geography column uses the abbreviated country code.

Replace values by using a regular expression#

Use regular expressions to replace multiple matching values with a single standardized value.

In this example, you group all values in the Market & Geography column that end with CA into a single category called Canada Market.

1. On the Transform tab, select Find and Replace.
2. In Target Columns, select Market & Geography.
3. In Find, enter .*,CA$.
4. Select Use Regular Expression.
5. In Replace With, enter Canada Market.
6. Select Apply.

The regular expression .*,CA$ matches any value that ends with CA.

After you apply the transformation, all matching values appear as Canada Market.

Match case options#

The Find and Replace transformation supports the following matching methods:

• Fuzzy Match: Finds values that approximately match the search text.
• Fuzzy Case Match: Finds approximate matches while considering letter casing.
• Match Full String: Replaces values only when the entire value matches the search text.
• Use Regular Expression: Uses regular expression patterns to identify matching values.