5 Ways to Clean Messy Data Without Writing Code

Real-world data is messy. Names are inconsistently capitalized, dates are in three different formats, columns have blank cells, and somehow there are 47 duplicate rows. Before you can analyze anything, you need to clean it up.

Here are five common data quality issues and how to fix them in ExploreMyData without writing a single line of code.

1. Remove duplicate rows

Duplicates are the most common data quality issue. They inflate counts, skew averages, and break joins.

How to fix:

1. Click the green + in the Pipeline panel and select Remove Duplicates from the Filter & Sort group.
2. Choose which columns to check for duplicates (or leave empty to check all columns).
3. Click Apply. Duplicate rows are removed, keeping the first occurrence.

The pipeline shows the step as SELECT DISTINCT ON (...) or SELECT DISTINCT * depending on your selection.

2. Fix inconsistent text

"New York", "new york", "NEW YORK", " New York ": these are all the same city, but they'll be treated as four different values in any group-by or filter.

How to fix:

1. Click the green + in the Pipeline panel and select Text Transform from the Transform group.
2. Choose the column (e.g., "city").
3. Select "trim" to remove leading/trailing whitespace.
4. Apply, then add a second Text Transform step with "lowercase" (or "capitalize") to normalize case.

Two steps, four values collapsed into two:

A group-by on the before column reports four cities. On the after column it reports two, which is the answer you actually wanted. Text Transform writes back into the same column and keeps it in its original position in the grid.

For more specific fixes, use Find & Replace from the Transform group. Select the column, enter the value to find (e.g., "NY") and the replacement (e.g., "New York"). This uses DuckDB's REPLACE() function.

3. Fill missing values

Blank cells (NULLs) can break calculations and create gaps in charts. ExploreMyData offers three fill strategies.

How to fix:

1. Click the green + in the Pipeline panel and select Fill Missing from the Data group.
2. Choose the column with missing values.
3. Pick a method:
   • Literal: replace NULLs with a specific value (e.g., "Unknown" or "0")
   • Forward fill: carry the nearest earlier non-empty value down
   • Backward fill: pull the nearest later non-empty value up

Forward and backward fill use DuckDB's window functions (LAST_VALUE() and FIRST_VALUE() with IGNORE NULLS) under the hood, so a run of consecutive blanks fills all the way through rather than one row deep.

4. Convert column types

A "price" column imported as text can't be summed or averaged. A "date" column stored as a string won't sort chronologically.

How to fix:

1. Click the green + in the Pipeline panel and select Convert Type from the Transform group.
2. Choose the column.
3. Select the target type. The dropdown offers three broad choices: text, numeric, or date.

ExploreMyData uses TRY_CAST(), which returns NULL for values that can't be converted instead of throwing an error. This is safer than a hard CAST.

Text to numeric does some cleanup first. Currency symbols ($, €, £, ₹), thousands separators, percent signs, and accounting-style parenthesised negatives are stripped before the cast, so "$1,234.56" becomes 1234.56 and "(500)" becomes -500. Text to date leaves the "Auto-detect date format" checkbox on by default, which tries a long list of common layouts including DD/MM/YYYY and MM/DD/YYYY.

5. Split or combine columns

A "full_name" column that should be "first_name" and "last_name". Or "city" and "state" columns that should be a single "location".

To split:

1. Click the green + in the Pipeline panel and select Split Column from the Columns group.
2. Choose the column and the delimiter (e.g., " " for space, "," for comma).
3. Set the number of parts. The result creates new columns named full_name_1, full_name_2, and so on.

Splitting full_name on a space into 2 parts:

Look at the third row. Split takes the nth piece between delimiters, so a three-word name puts "Jane" in part 2 and drops "Watson" entirely. If your names vary in length, either raise the part count, or use Extract Text from the Transform group with the "before delimiter" and "after delimiter" methods, which give you "Mary" and "Jane Watson".

To combine:

1. Click the green + in the Pipeline panel and select Combine Columns from the Columns group.
2. Build the result in the chip box, which reads "Type text or select a column...". Pick the city column, type , as literal text, then pick state. Each becomes a chip, and they are concatenated left to right.
3. Use "Apply results into" to name the new column, or to write the result over an existing one. NULLs in any chip become empty strings.

There is no separate separator field, which trips people up at first. The separator is just a text chip you type between two column chips, and that turns out to be more flexible: you can put a prefix, a suffix, and different separators in the same expression.

Everything is a pipeline step

Every cleaning operation becomes a step in your pipeline. You can see the exact SQL generated, delete steps you don't need, and the pipeline rebuilds automatically. This means your data cleaning is documented and reproducible.

Start cleaning your data →