---
title: "Null vs blank vs zero in data cleaning | Oplema"
description: "Learn to clean stray spaces and case, tell null from blank from zero and decide when to keep, fill or exclude missing values, with COALESCE in SQL."
url: "https://oplema.com/learn/data-preparation/cleanse-and-nulls"
updated: "2026-11-04"
---

Lesson 6 of 15 · Lab

# Null vs blank vs zero: cleaning missing values

Cleaning fixes how values are written without changing what they mean, by trimming stray spaces and making case consistent. Missing values are a separate decision. A null means unknown, a blank is empty text and a zero is a real amount.

This lab's 18 rows come from [fictional Vandelay Coffee Supply](https://oplema.com/learn/data-preparation/what-is-data-prep), which writes `booked` with capitals and with a trailing space, so a filter on exactly `booked` would miss three invoices worth €3,040.

Cleanse could lowercase every text column at once. What would that do to `customer_id` C2?

It becomes c2, and no longer matches the customer register. Case is meaningful in a key and only noise in a status, so the rule has to name the column.

## Lowercase a single column

1. Select **Summarize**, the last block of this live flow in [Strata](https://oplema.com/strata), Oplema's browser app for data prep. **Trim Whitespace** is already on, and `status` still has five values, including `Booked` and `BOOKED`.
2. Select **Cleanse**. Open **Per-Column Overrides**, select the arrow at the right of `status`, scroll down and set its **Modify Case** to **Lower**.
3. Select **Summarize**. The capitalized spellings should have merged into `booked`, with `pending` and `cancelled` untouched.
4. Select **Cleanse** and scroll the preview to INV-1014. `amount_eur` is still NULL, not 0, and `customer_id` is still uppercase.

How the statuses should add up

`booked` 15, `pending` 2, `cancelled` 1, which adds back to 18. Trim also fixed INV-1012's key, `C2`, which would otherwise have failed to find its customer.

## Cleaning rules belong to columns

One block holds two rules here. Trim applies to every text column, because a stray space carries no meaning. Lowercase applies only to `status`, through an override.

Cleansing never decides which row is true, though. INV-1008 still has two clean rows, and they still disagree, which [finding duplicate rows](https://oplema.com/learn/data-preparation/unique-and-duplicates) settles. See the [Cleanse reference](https://oplema.com/docs/strata/prep/cleanse).

## Keep, fill or exclude a missing value

Take the values 10, a null and 20. The average skips the null and comes to 15. Fill the null with zero and the average drops to 10.

Three choices for a missing value
| Choice | When |
| --- | --- |
| Keep it null | The value is unknown, and the row still counts |
| Fill it | A stated rule gives the value, as when no discount recorded means none |
| Exclude the row | It can't serve the question, and you record that you removed it |

INV-1014 stays null. Its price really is unknown, and the sum simply skips it. Someone in Finance still has to price that invoice, so ask. For rule-based filling, see [Impute](https://oplema.com/docs/strata/prep/impute).

Common mistakes follow from mixing these up. Filling with zero quietly lowers every average. Treating `TBC`, `N/A` or an empty string as data leaves a column that looks complete. And merging similar names, such as two companies with one word in common, changes meaning, so it goes beyond cleaning.

## TRIM, LOWER and COALESCE

In SQL, `LOWER(TRIM(status))` cleans the text, `NULLIF(status, '')` turns a blank into a null, and `COALESCE(discount, 0)` fills a null with zero. In Excel, `=LOWER(TRIM(A2))` does the same job in a helper column, and `=IF(A2="", 0, A2)` fills a blank. Excel's `AVERAGE` skips empty cells but counts zeros, like SQL's `AVG`.
