Skip to content

isnull.md

Latest commit

 

History

History
146 lines (99 loc) · 5.99 KB

isnull.md

File metadata and controls

146 lines (99 loc) · 5.99 KB
title Isnull

Isnull

isNull checks if a value is a null, a special kind of placeholder that's used by a database when something is missing or unknown.

Syntax

isNull(text column)

You can use isNull in custom filters, or as the condition for conditional aggregations CountIf and SumIf. To create a custom column using isNull, you must combine isNull with another function that accepts boolean values, like case.

How Metabase handles nulls

In Metabase tables, nulls are displayed as blank cells. Additionally, for string columns, empty strings and strings containing only whitespace characters will be displayed as blank as well.

The table below shows you examples of the output of isNull.

Metabase shows Database value isNull(value)
null true
"" (empty string) false*
" " (whitespace) false
kitten "kitten" false

*In Oracle and Vertica databases, empty strings are treated as nulls instead.

Creating a boolean custom column

To create a custom column using isNull, you must combine isNull with another function. For example, if you want to create a custom column that contains true when the Discount column is null, and false otherwise, you can use the case expression :

case(isNull([Discount]), true, false)

Replacing null values with another value

Combine isNull with the case expression to replace missing information with something more descriptive:

For example, you can create a new custom column that will contain "Unknown feedback" when the original [Feedback] column is null, and the actual feedback value when [Feedback] is has a value. The custom expression to do it is:

case(isNull([Feedback]), "Unknown feedback.", [Feedback])
Feedback case(isNull([Feedback]), "Unknown feedback.", [Feedback])
null "Unknown feedback."
"" ""
"I like your style." "I like your style."

Accepted data types

Data type Works with isNull
String ✅
Number ✅
Timestamp ✅
Boolean ✅
JSON ✅

Limitations

  • In Metabase, you must combine isNull with another expression that accepts boolean arguments (i.e., true or false).
  • isNull only accepts one value at a time. If you need to deal with blank cells across multiple columns, see the coalesce expression.
  • If isNull doesn't seem to do anything to your blank cells, you might have empty strings. Try the isEmpty expression instead.

Related functions

This section covers functions and formulas that can be used interchangeably with the Metabase isNull expression, with notes on how to choose the best option for your use case.

All examples below use the table from the Replacing null values example:

Feedback case(isNull([Feedback]), "Unknown feedback.", [Feedback])
null "Unknown feedback."
"" ""
"I like your style." "I like your style."

SQL

In most cases (unless you're using a NoSQL database), questions created from the query builder are converted into SQL queries that run against your database or data warehouse.

CASE WHEN Feedback IS NULL THEN "Unknown feedback",
     ELSE Feedback END

is equivalent to the Metabase isNull expression:

case(isNull([Feedback]), "Unknown feedback.", [Feedback])

Spreadsheets

Spreadsheet #N/As are the equivalent of database nulls (placeholders for "unknown" or "missing" information).

Assuming our sample feedback column is in a spreadsheet where "Feedback" is in column A, then the formula

=IF(ISNA(A2), "Unknown feedback.", A2)

is equivalent to the Metabase isNull expression:

case(isNull([Feedback]), "Unknown feedback.", [Feedback])

Python

Numpy and pandas use NaNs or NAs instead of nulls.

Assuming our sample feedback column is in a dataframe column called df["Feedback"]:

df["Custom Column"] = np.where(df["Feedback"].isnull(), "Unknown feedback.", df["Feedback"])

is equivalent to the Metabase isNull expression:

case(isNull([Feedback]), "Unknown feedback.", [Feedback])

Further reading