Copying a Column and Transforming the Copy

You need to normalize company names to lowercase for deduplication. But you also need to keep the original casing for the report that goes to the client. You want to strip special characters from a product SKU for matching, but the original SKU still needs to appear in the export. You need both versions of the same data, side by side.

The solution is simple: copy the column first, then transform the copy. The original stays untouched. Here's how to do it in ExploreMyData.

Step 1: Copy the column

Click the green + in the Pipeline panel and select Copy Columns from the Columns group. Choose the source column (e.g., company_name) and give the copy a name that describes its purpose, like company_name_normalized.

The generated SQL is straightforward:

SELECT *, "company_name" AS "company_name_normalized" FROM "customers"

The new column appears at the end of your table with identical values to the original.

Step 2: Transform the copy

Now apply your transformations to the copy, leaving the original alone. Select Text Transform from the Transform group, choose the company_name_normalized column, and pick "lowercase".

Same four rows, same order, one column changed:

The original column is preserved exactly as it was. The normalized copy is ready for matching.

Chaining more transformations on the copy

Lowercasing is just the first step. You can keep stacking transformations on the copy:

1. Apply Text Transform with "trim" to remove leading and trailing whitespace.
2. Use Find & Replace on the copy to remove punctuation. For example, replace "." with "" to strip periods, or replace " Corp" with "" to normalize company suffixes.
3. Apply another Find & Replace to collapse double spaces into single spaces.

Each operation targets only the company_name_normalized column. The original company_name stays pristine through every step.

Real-world example: deduplication prep

Say you're trying to find duplicate companies in a CRM export. The original names have inconsistent formatting: "Acme Corp.", "ACME Corp", "acme corp", and " Acme Corp " are all the same company but look different to any exact-match comparison.

The workflow:

1. Copy Columns: company_name to company_match_key.
2. Text Transform: lowercase company_match_key.
3. Text Transform: trim company_match_key.
4. Find & Replace: replace "." with "" in company_match_key.
5. Remove Duplicates: deduplicate on the company_match_key column.

Now "Acme Corp.", "ACME Corp", and " Acme Corp " all collapse into one row, and that row still carries the original formatting in the company_name column. Worth knowing: Remove Duplicates keeps one row per key and which of the matching rows survives is arbitrary, so don't count on getting the tidiest spelling of the three.

Other use cases for copy-then-transform

Two more places this earns its keep:

• Email matching: copy email to email_key, then lowercase and trim the copy. The original keeps whatever capitalization the customer typed, which is what you want in a greeting.
• Prices you need to do math on: copy price_display (values like "$1,299.00") to price_value and run Convert Type to numeric on the copy. No Find & Replace needed first: the text-to-numeric conversion strips currency symbols, commas and spaces on its own.

Why not just transform in place?

You could apply Text Transform directly to the original column. But then you've lost the original data. In ExploreMyData, pipeline steps are non-destructive and you can delete them, but if your workflow needs both the original and the transformed version in the final output, transforming in place doesn't work. Copy first, transform second.

It's two pipeline steps instead of one, and the SQL is clear about what's happening: first a copy, then a modification of the copy. Anyone reviewing the pipeline later can see exactly what was done and why.

Try the copy-and-transform pattern →