# Turn values into nulls

Shape / columns

A variant of [Fill in missing values](https://meridian.online/operations/fill-in-missing-values).

Mark a given value, or the selected cells, as missing.

Page: <https://meridian.online/operations/fill-in-missing-values/turn-values-into-nulls>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

turn a given value, or chosen cells, into nulls

```sql
CREATE TEMP TABLE raw AS SELECT * FROM (VALUES
  (1, 10, 'ok'), (2, -999, 'n/a'), (3, 30, 'N/A'), (4, -999, 'fine'), (5, 50, 'n/a')
) t(id, qty, note);
CREATE TEMP TABLE result AS
SELECT id,
       nullif(qty, -999) AS qty,
       nullif(note, 'n/a') AS note,
       CASE WHEN id IN (3, 4) THEN NULL ELSE note END AS note_cells
FROM raw;
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT list(qty ORDER BY id) FROM result) = [10, NULL, 30, NULL, 50] AND (SELECT list(note ORDER BY id) FROM result) = ['ok', NULL, 'N/A', 'fine', NULL] AND (SELECT list(note_cells ORDER BY id) FROM result) = ['ok', 'n/a', NULL, NULL, 'n/a']) THEN 'as expected' ELSE error('the sentinel -999 and the text n/a were not turned into nulls, or the chosen cells in rows 3 and 4 were not') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Apache Hop: [`Null If`](https://hop.apache.org/manual/latest/pipeline/transforms/nullif.html) (read 2026-09-27)
- Ibis: [`NullIf`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/generic.py) (read 2026-09-27)
- OpenRefine: [`To empty string`](https://openrefine.org/docs/manual/cellediting#data-type-transforms) (read 2026-09-27)
- OpenRefine: [`To null`](https://openrefine.org/docs/manual/cellediting#data-type-transforms) (read 2026-09-27)
- Pentaho Data Integration: [`Null if...`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27)
- RapidMiner Studio: [`Declare Missing Value`](https://docs.rapidminer.com/latest/studio/operators/cleansing/missing/declare_missing_value.html) (read 2026-09-27)
- RapidMiner Studio: [`Declare Missing Value (In Database)`](https://docs.rapidminer.com/latest/studio/operators/extensions/In-Database%20Processing/indb_cleansing/indb_declare_missing.html) (read 2026-09-27)
- VisiData: [`delete-cell`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/clipboard.py#L207) (read 2026-09-23)
- VisiData: [`delete-cells`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/clipboard.py#L208) (read 2026-09-23)