Coalesce

coalesce looks at the values in a list (in order), and returns the first non-null value.

This function is useful when you want to:

• fill in missing data,
• consolidate data from multiple columns, or
• create calculations across multiple columns.

Filling in empty or null values

You may want to fill in empty or null values if you have:

• Sparse data.
• null values created by a left join (the example shown above).

For a more detailed example, see Filling in data for missing report dates.

Consolidating values from different columns

Creating calculations across different columns

Calculations in Metabase will return null if any of the input columns are null. This is because null values in your data represent “missing” or “unknown” information, which isn’t necessarily the same as an amount of “0”. That is, adding 1 + “unknown” = “unknown”.

If you want to treat “unknown” values as zeroes (or some other value that means “nothing” in your data), we recommend using coalesce to wrap the columns used in your calculations.

Accepted data types

Limitations

Use the same data types within a single coalesce function. If you want to coalesce values that have different data types:

• Use the SQL CAST operator.
• Change the data type from the Table Metadata page.

If you want to use coalesce with JSON or JSONB data types, you’ll need to flatten the JSON objects first. For more information, look up the JSON functions that are available in your SQL dialect. You can find some common SQL reference guides here.

Related functions

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

Metabase expressions

• case

Other tools

• SQL
• Spreadsheets
• Python

All examples use the custom expression and sample data from the Consolidating values example:

Case

The Metabase case expression

case(ISBLANK([Notes]) = FALSE AND ISBLANK([Comments]) = FALSE, [Notes],
     ISBLANK([Notes]) = TRUE  AND ISBLANK([Comments]) = False, [Comments],
     ISBLANK([Notes]) = FALSE AND ISBLANK([Comments]) = TRUE,  [Notes],
     ISBLANK([Notes]) = TRUE  AND ISBLANK([Comments]) = TRUE,  "No notes or comments")

is equivalent to the Metabase coalesce expression:

coalesce([Notes], [Comments] "No notes or comments.")

coalesce is much nicer to write if you don’t mind taking the first value when both of your columns are non-blank. Use case if you want to define a specific output (e.g., if you want to return “I have a note and a comment” instead of “I have a note”.).

SQL

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

The SQL coalesce function

SELECT
    COALESCE(notes, comments, "no notes or comments")
FROM
    sample_table;

is equivalent to the Metabase coalesce expression:

coalesce([Notes], [Comments] "No notes or comments.")

Spreadsheets

If your notes and comments table is in a spreadsheet where “Notes” is in column A, and “Comments” is in column B, then the formula

=IF(ISBLANK($A2),$B2,IF(ISBLANK($B2),$A2,"No notes or comments."))

is equivalent to the Metabase coalesce expression:

coalesce([Notes], [Comments] "No notes or comments.")

Alternatively, you may be used to working with a INDEX and MATCH in an array formula if you’re “coalescing” data across three or more columns in a spreadsheet.

Python

Assuming the notes and comments table is in a dataframe called df, the combination of pandas functions combine_first() and fillna()

df['custom_column'] = df['notes'].combine_first(df['comments'])\
                                 .fillna('No notes or comments.')

are equivalent to the Metabase coalesce expression:

coalesce([Notes], [Comments] "No notes or comments.")

Further reading

• Custom expressions documentation
• Custom expressions tutorial