The Find and Replace transformation allows for you to search for a value in your Table or Dataset and, if found, replace either it or another value as specified. Like all other Transformations, the Find and Replace transformation can be applied to either a Table or a Dataset.
Follow the steps outlined below to create a Find and Replace Transformation:
1. Open the Table or Dataset you would like to transform by clicking on its title, under the Tables or Datasets heading.
2. When the Table or Dataset opens, a sidebar titled Transformations will appear on the left side of the screen. Any existing transformations will be displayed under that heading.
To create a new transformation, click the + in the sidebar (note: if there are several existing transformations, you will need to scroll down to view the +).
Select Find And Replace from the list that appears. This will open the Find And Replace Transformation editor.
3. Name the Transformation you would like to create (this helps keep your transformations organized).
4. Find And Replace allows you to search for a set value in the Source Column, and replace the value in a Target Column.
Select the Source Column and the Target Column from their respective drop-down lists (note, both the Source and Target column can be the same column if desired).
5. Set the conditions for the Find And Replace action from the drop-down list next to the source column header.
Any value entered in the Find field (Source column) is not case sensitive. However, any value entered in the Replace Value With field (Target column) is case sensitive and will be changed to exactly what you enter in the Replace Value With field.
6. After you have identified the condition for the source column, set the value of the Target Column if the condition is met.
You can create multiple actions in the same statement by adding another row in the statement.
Note - you must specify the value to be found in the Source column. There is no ‘wild card’ option to find all other values not previously specified.
7. When your Find And Replace criteria is set, click the Publish button in the top right corner to save the Transformation. If you wish to cancel this Transformation, click the Revert button in the top right corner.
Once your Transformation has been published, it will be applied to your Table or Dataset. This process can take several minutes, but once it is complete, the transformed data should show up in your Table or Dataset. If you create a new column in an existing Dataset, specifically one that is referenced in an active Workbook, you may need to open the Column Properties tab and make sure the newly created column is searchable. This will make the column available for use in Workbooks.
Note - all of the Transformations in Numetric are non-destructive, meaning they do not permanently change the data. Any transformation can be easily undone by deleting it from the list of Transformations, reverting your data to its previously un-transformed state.