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
orfalse
). isempty
only accepts one value at a time. If you need to deal with empty strings from multiple columns, you’ll need to use multipleisempty
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
Read docs for other versions of Metabase.