Bulk Replace

Tidy a messy category column. Tick the spellings that mean the same thing, and give them one name.

The problem it solves

People type the same thing in many ways. A delivery file can hold Bengaluru, Bangalore, bengaluru and Bnagalore in one city column. Any count you run treats them as four cities.

Bulk Replace shows you every value in the column, with a row count for each one. You tick the variants, name the group, and the whole set becomes one value.

Build your first group

  1. Type bulk replace in the Search transforms box, in the Pipeline panel.
  2. Select Bulk Replace in the results.
  3. Open Select column and choose your column. The list holds text columns only.
  4. Look at the All Values list on the left. The most common value sits at the top.
  5. Tick every spelling that belongs together.
  6. Type the correct name in the Group name box on the right. For example, type Bengaluru.
  7. Click + Create. The group appears in the list below, with its value count.
  8. Repeat steps 5 to 7 for the next group.
  9. Click Apply.

A value that joins a group goes grey in the list. You cannot tick it twice. Click the cross on a group to break it up.

Find long value lists faster

The Search values... box filters the list. Type ban to see only the values that hold those letters. The list shows up to 200 values at a time, so the search matters on a wide column.

Let the app find near-duplicates

Click the Similar Suggestions tab. The app compares your values and groups the pairs that differ by one character. A typo such as Bnagalore shows up next to Bangalore.

  1. Click Similar Suggestions. The app builds the list on the first click.
  2. Read each card. It shows the proposed name and the first values in the group.
  3. Click + Accept on a card you agree with. The button then reads Added.
  4. Go back to All Values and build any group the suggestions missed.

The check is strict. It catches single-character typos, and case differences. It will not pair Bengaluru with Bangalore, because those differ by more than one character. Build that group yourself.

What happens to the rest

  • A value you did not group keeps its own text. Nothing is lost.
  • No rows are added or removed.
  • The result replaces your column. It keeps the same name.
  • The result is always a text column, because group names are text. Group a number column, and the numbers come back as text.
  • The column keeps its position in the grid.

For SQL users

Two groups on a city column run as:

SELECT "order_id", "amount", CASE WHEN CAST("city" AS VARCHAR) IN ('Bengaluru', 'Bangalore', 'bengaluru') THEN 'Bengaluru' WHEN CAST("city" AS VARCHAR) IN ('Mumbai', 'Bombay') THEN 'Mumbai' ELSE CAST("city" AS VARCHAR) END AS "city" FROM data
Try Bulk Replace with sample data →

Related Operations