A Complete Guide to Handling NULL and Empty Values

You put an "is Empty" filter on the region column and get 247 rows. Then you search the value list and find 83 more saying "N/A", another 41 saying "n/a", nineteen dashes, and twelve cells containing the literal word "null". Four hundred and two rows mean "we don't know", and your database sees six unrelated values.

This is one of the most common problems in real-world data, and it's frustrating because it's invisible. The cells aren't obviously wrong. They just quietly break your counts, your filters, and your group-bys.

Here's how to find every flavor of "missing" in your data and get them all consistent using ExploreMyData.

The five types of "missing"

Before you fix anything, you need to understand what you're dealing with. In a typical messy dataset, "missing" can look like:

The distinction between NULL and an empty string trips up even experienced analysts. NULL means "no value was provided". An empty string means "a value was provided, and it's nothing". Most of the time you want to treat them the same way. But WHERE col IS NULL won't catch empty strings, and WHERE col = '' won't catch NULLs.

Which is exactly why ExploreMyData doesn't give you an "is NULL" operator on a text column. The operator list is is, is not, starts with, ends with, does NOT start with, does NOT end with, contains, is Empty and is NOT Empty, and "is Empty" is deliberately defined as both:

(region IS NULL OR region = '')

On a numeric or date column the same operator generates a plain region IS NULL, since there's no empty string to worry about. Keep that difference in mind: "is Empty" means one thing on text and a narrower thing on numbers.

Step 1: Find the missing values

Start by figuring out what you're dealing with. Click the column header to open Column Explorer. For a text column it lists every distinct value with its count, most frequent first, and NULLs get their own italic (null) row. Anything sitting in that list that means "missing", whether it's "N/A" or a dash, is now visible and countable.

One thing the explorer won't do is separate a NULL from an empty string at a glance, because an empty string renders as an empty row. To split them, add a Filter step, switch the condition builder to the SQL Expression tab and enter region = ''. The row count tells you how many empty strings you have; the explorer's (null) count tells you the NULLs; the "is Empty" operator gives you the sum of the two. Delete the filter step when you're done.

Step 2: Normalize placeholder text to NULL

The goal is to collapse all the different representations of "missing" into one consistent thing. Usually that means converting them all to actual NULL, because NULL is what SQL functions like COALESCE, COUNT, and IS NULL understand.

The obvious move is Find & Replace with the replacement box left blank, and it is the wrong one. Replacing with nothing produces an empty string, not a NULL. The generated SQL is a plain REPLACE(region, 'N/A', ''), and '' is a value. You've turned 83 visible "N/A" cells into 83 invisible empty strings. Your NULL count doesn't move.

Two more reasons not to reach for it here. Find & Replace matches substrings by default, so finding - would quietly turn "Mid-West" into "MidWest" in every row that has one. There is an Entire cell checkbox that switches it to exact matching, and for placeholder cleanup you always want it ticked. And Case sensitive is on by default, so "N/A" and "n/a" are two separate passes unless you turn it off.

What actually produces NULLs is Update Values. Select the region column, open the condition builder, and add one condition per placeholder joined with OR: region is "N/A", region is "n/a", region is "null", region is "None", region is "-", and region is Empty to sweep up the NULLs and empty strings in the same pass. Then leave the Value box blank. A blank value emits NULL, which is what you want:

CASE WHEN (region = 'N/A' OR region = 'n/a' OR region = 'null' OR region = 'None' OR region = '-' OR (region IS NULL OR region = '')) THEN NULL ELSE region END

One step, every variant, and the "is Empty" branch means you don't have to know in advance whether your blanks were NULLs or empty strings.

If you'd rather type it, a SQL Query step does the same job more compactly, and picks up case variants you haven't seen yet:

SELECT * REPLACE (CASE WHEN LOWER(TRIM(region)) IN ('n/a', 'null', 'none', '-', '') THEN NULL ELSE region END AS region) FROM pipeline_output

For the single case of "turn empty strings into NULL and leave everything else alone", NULLIF(region, '') is the shortest form there is.

Step 3: Decide what to do with NULLs

Now all your missing values are actual NULLs. But you still need to decide: keep them as NULL, or fill them with something? It depends on the column and what you're doing with it.

Option A: Leave them as NULL. This is often the right call. NULLs are excluded from AVG(), SUM(), and COUNT(col) automatically. If the data is genuinely missing, it's honest to keep it that way.

Option B: Fill with a default. Use Fill Missing and pick "literal" mode. For a region column, maybe you fill with "Unknown". For a discount column, maybe you fill with "0". The SQL uses COALESCE(col, 'Unknown'). This is exactly why step 2 comes first: COALESCE replaces NULL and nothing else, so any empty string you didn't convert stays an empty string and quietly becomes its own category in your group-by.

Option C: Fill from adjacent rows. For time-series data where a value carries forward (like a subscription tier that doesn't change every month), use forward fill. The previous non-NULL value fills in the gaps.

Conditional cleanup with Update Values

Sometimes the logic is more nuanced. Maybe you want to fill NULLs in the state column with "Unknown", but only when the country column is "US". For international rows, you want to leave them NULL.

Use Update Values for this. Two conditions joined with AND: state is Empty, country is "US". Value: "Unknown". The condition builder emits:

CASE WHEN ((state IS NULL OR state = '') AND country = 'US') THEN 'Unknown' ELSE state END

Note that the "is Empty" leg expands to both tests, so this catches an empty-string state as well as a NULL one even if you skipped the normalization step. That's the operator doing you a favour, and it's also why the generated SQL is longer than what you'd have written by hand.

The order matters

A good cleanup sequence for null handling:

1. Normalize first. Convert "N/A", "n/a", "null", "None", "", "-" to actual NULL. Now you have one type of missing value, not six.
2. Then decide per column. Some columns should stay NULL. Some should get a default. Some should be forward-filled. Handle each one based on what makes sense for that data.
3. Verify. Open Column Explorer again. The NULL count should match what you expect. The phantom values like "N/A" should be gone from the value distribution.

Every step is reversible

All of this lives in your pipeline. If you realize you were too aggressive - maybe "-" actually means something in one particular column - just delete that pipeline step. The data rebuilds without it. You're never making destructive changes to the original file.

Clean up your null values →