# Drop rows with missing or error values

Shape / rows

A variant of [Filter rows](https://meridian.online/operations/filter-rows).

Drop, or keep only, the rows that hold nulls, empty cells, NaNs or error values.

Page: <https://meridian.online/operations/filter-rows/drop-rows-with-missing-values>

## Status

Runs as a SQL step.

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

## The DuckDB step

drop, or keep only, rows with nulls, empty cells, NaNs or error values

```sql
CREATE TEMP TABLE readings AS SELECT * FROM (VALUES
  (1, 'north', 1.5,            '10'),
  (2, NULL,    2.0,            '20'),
  (3, '',      3.0,            '30'),
  (4, 'west',  'NaN'::DOUBLE,  '40'),
  (5, 'east',  5.0,            '#DIV/0!'),
  (6, 'south', 6.0,            '60'),
  (7, 'north', 7.0,            NULL),
  (8, 'west',  NULL,           '80')
) t(id, site, score, raw);
-- a row is bad when any cell is null, empty, NaN, or text that does not read as a number
CREATE TEMP TABLE flagged AS
SELECT *, (site IS NULL OR site = '' OR score IS NULL OR isnan(score) OR raw IS NULL OR TRY_CAST(raw AS DOUBLE) IS NULL) AS is_bad
FROM readings;
CREATE TEMP TABLE clean AS SELECT id, site, score, raw FROM flagged WHERE NOT is_bad;
CREATE TEMP TABLE bad AS SELECT id, site, score, raw FROM flagged WHERE is_bad;
SELECT * FROM clean ORDER BY id;
SELECT * FROM bad ORDER BY id;
SELECT CASE WHEN ((SELECT list(id ORDER BY id) FROM clean) = [1, 6] AND (SELECT list(id ORDER BY id) FROM bad) = [2, 3, 4, 5, 7, 8]) THEN 'as expected' ELSE error('the clean rows were not 1 and 6, or the rows with a null, an empty cell, a NaN or an error value were not 2, 3, 4, 5, 7 and 8') 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).

- EasyMorph: [`Filter by type`](https://help.easymorph.com/doku.php?id=transformations:filterbytype) (read 2026-09-27)
- EasyMorph: [`Remove empty rows`](https://help.easymorph.com/doku.php?id=transformations:removeemptyrows) (read 2026-09-27)
- AWS Glue: [`Remove null rows`](https://docs.aws.amazon.com/glue/latest/dg/transforms-remove-null-rows.html) (read 2026-09-27)
- Ibis: [`DropNull`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/relations.py) (read 2026-09-27)
- Polars: [`DataFrame.drop_nans`](https://docs.pola.rs/api/python/stable/reference/dataframe/modify_select.html) (read 2026-09-27)
- Polars: [`DataFrame.drop_nulls`](https://docs.pola.rs/api/python/stable/reference/dataframe/modify_select.html) (read 2026-09-27)
- Power Query M: [`Table.RemoveRowsWithErrors`](https://learn.microsoft.com/en-us/powerquery-m/table-functions#row-operations) (read 2026-09-27)
- Power Query M: [`Table.SelectRowsWithErrors`](https://learn.microsoft.com/en-us/powerquery-m/table-functions#row-operations) (read 2026-09-27)
- RapidMiner Studio: [`Filter Examples with Missing Values`](https://docs.rapidminer.com/latest/studio/operators/extensions/Operator%20Toolbox/blending/filter_missing_examples.html) (read 2026-09-27)
- Tableau Prep: [`Null Values`](https://help.tableau.com/current/prep/en-us/prep_filter.htm) (read 2026-09-27)
- Trifacta: [`Filter mismatched`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27)
- Trifacta: [`Filter missing`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27)