Skip to content

isempty.md

Latest commit

 

History

History
141 lines (95 loc) · 5.69 KB

isempty.md

File metadata and controls

141 lines (95 loc) · 5.69 KB
Raw
title Isempty

Isempty

isempty checks whether a value in a string column is an empty string ("") or null. Calling isempty on a non-string column would cause an error.

Syntax

isempty(text column)

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

How Metabase handles empty strings and null values

In Metabase, columns with string data types will display blank cells for empty strings, strings of whitespace characters, or null values (if the column is nullable in your database). The table below shows you examples of the output of isempty.

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

Creating a boolean custom column

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

case(isempty([Feedback]), true, false)

Replacing empty strings with another value

You can combine isempty with the case expression to replace empty strings with something more descriptive.

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

case(isempty([Feedback]), "No feedback.", [Feedback])
Feedback case(isempty([Feedback]), "No feedback.", [Feedback])
"" "No feedback."
null "No feedback."
"I like your style." "I like your style."

Accepted data types

Data type Works with isempty
String ✅
Number ❌
Timestamp ❌
Boolean ❌
JSON ❌

Limitations

  • To create a custom column you must combine isempty with another expression that accepts boolean arguments (i.e., true or false).
  • isempty only accepts one value at a time. If you need to deal with empty strings from multiple columns, you'll need to use multiple isempty expressions with the case expression.

Related functions

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

All examples below use the table from the Replacing empty strings example:

Feedback case(isempty([Feedback]), "No feedback.", [Feedback])
"" "No feedback."
null "No 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 = "" OR Feedback IS NULL) THEN "No feedback"
     ELSE Feedback END

is equivalent to the Metabase isempty expression:

case(isempty([Feedback]), "No feedback.", [Feedback])

Spreadsheets

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

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

is equivalent to the Metabase isempty expression:

case(isempty([Feedback]), "No feedback.", [Feedback])

Python

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

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

is equivalent to the Metabase isempty expression:

case(isempty([Feedback]), "No feedback.", [Feedback])

Further reading