Remapping Category Codes to Readable Labels
You just got a database export. The category column doesn't say "Electronics" or "Clothing" - it says "CAT_01" and "CAT_02". The region column says "REG_NE" instead of "Northeast". The status column says "STS_A" instead of "Active".
These codes made sense to whoever designed the database schema. They're meaningless to anyone trying to read the data. And you need readable labels before you can share this with anyone, build a chart, or do a group-by that makes sense.
You could do a dozen Find & Replace operations, one for each code. Or you could open a code editor and write CASE WHEN statements. Both work, both are tedious, and both are easy to get wrong when you have 20+ codes to remap.
The messy reality of code columns
Here's what a typical export looks like:
| product_id | category | region | status |
|---|---|---|---|
| 1001 | CAT_01 | REG_NE | STS_A |
| 1002 | ELEC | REG_SE | STS_D |
| 1003 | CAT_02 | REG_MW | STS_A |
| 1004 | CLTH | REG_W | STS_P |
Notice the extra wrinkle: "CAT_01" and "ELEC" both mean Electronics. Different systems used different codes for the same thing. This is extremely common when data comes from merged systems, legacy migrations, or multiple teams.
Bulk Replace: remap many codes at once
Open the toolbar in ExploreMyData and select Bulk Replace from the Transform group. Choose the column you want to remap (e.g., "category").
Bulk Replace works with groups. Each group has a name (the readable label you want) and a list of values that should map to that label:
- Electronics: CAT_01, ELEC
- Clothing: CAT_02, CLTH
- Home & Garden: CAT_03, HOME
- Sports: CAT_04, SPRT
| Output label | Values that map to this label |
|---|---|
| Electronics | CAT_01, ELEC |
| Clothing | CAT_02, CLTH |
| Home & Garden | CAT_03, HOME |
| Sports | CAT_04, SPRT |
Bulk Replace groups: each output label consolidates all incoming code variants into one readable value.
Click Apply. Every "CAT_01" and "ELEC" becomes "Electronics". Every "CAT_02" and "CLTH" becomes "Clothing". One operation, all remapped.
The generated SQL
The pipeline shows the SQL behind the operation:
CASE
WHEN category IN ('CAT_01', 'ELEC') THEN 'Electronics'
WHEN category IN ('CAT_02', 'CLTH') THEN 'Clothing'
WHEN category IN ('CAT_03', 'HOME') THEN 'Home & Garden'
WHEN category IN ('CAT_04', 'SPRT') THEN 'Sports'
ELSE category
END
The ELSE category at the
end preserves any values that don't match a group. If a code slipped through that you didn't
account for, it stays as-is rather than becoming NULL.
Remap multiple columns
The category column is fixed. Now do the same for region and status. Each one is a separate Bulk Replace step:
Region column:
- Northeast: REG_NE
- Southeast: REG_SE
- Midwest: REG_MW
- West: REG_W
Status column:
- Active: STS_A
- Discontinued: STS_D
- Pending: STS_P
Pipeline steps applied in sequence:
- 1Bulk Replace on
category- maps CAT_01/ELEC → Electronics, CAT_02/CLTH → Clothing, etc. - 2Bulk Replace on
region- maps REG_NE → Northeast, REG_SE → Southeast, REG_MW → Midwest, REG_W → West - 3Bulk Replace on
status- maps STS_A → Active, STS_D → Discontinued, STS_P → Pending
After three operations, the data reads like this:
| product_id | category | region | status |
|---|---|---|---|
| 1001 | Electronics | Northeast | Active |
| 1002 | Electronics | Southeast | Discontinued |
| 1003 | Clothing | Midwest | Active |
| 1004 | Clothing | West | Pending |
Readable. Shareable. Ready for analysis.
When codes come from a lookup table
If you already have a separate CSV or sheet that maps codes to labels, you could also use Join to bring in the labels from that file. But for quick remapping when you know the codes already, Bulk Replace is faster - no second file needed.
Handling codes you missed
After applying Bulk Replace, scan the column for any unmapped codes. The easiest way: use Count Unique Values from the Explore group. If you see "CAT_05" sitting alongside "Electronics" and "Clothing", you missed one. Edit the pipeline step to add it.
Because every operation is a pipeline step, you can always go back and update the mapping. Add a new code to an existing group, create a new group, or remove one that's wrong. The data recalculates.