Tableau offers users a number of ways to handle null or missing values when preparing a dataset or when presenting information in a visual. We’ll explain how to work with null values using field calculations, as well as how to handle them without having to manually update your dataset in every instance.
Null values are one of the most common problems to deal with in business intelligence and data preparation. Unfortunately, when they’re present, they can cause formulas to either error out or return incorrect information.
Let’s take a look at how to work with null values in Tableau!
Table of Contents
What are Null Values in Tableau?
Null values in Tableau refer to cells in a data source that have missing or unknown data. In the world of data, null values have a unique place in that they are specifically defined as no information. For example, a middle name field where a person does not have a middle name or the information was never collected.
Tableau shows null values as: null in a dataset, like in the screenshot below.
Here’s some key things to know about nulls in Tableau and why you should replace them:
- Null values are very common in raw data sets. A value may be null if it was not collected or left blank.
- Tableau will exclude rows with null values when calculating sums, averages, etc. This can skew results.
- Null values can create gaps in visualizations, especially when working with time series data.
- Nulls can lead to calculation errors or incorrect results in a number of visuals.
The best practice is to replace null values with some estimate or placeholder, typically nulls are replaced with a zero when working with a numeric field, or with a blank when working with a text field. However, there are instances where you will want to use other values.
Let’s take a look at how to replace nulls.
How to Replace Nulls in Tableau
To replace nulls in Tableau, create a new calculated column using the “ZN” function as part of a formula to automatically replace null values in numeric columns with a 0. To replace null values in text columns, use a combination of IF and ISNULL formulas to check if a value is null and to return the desired value.
When using these methods, it’s important to be aware of a couple of quirks with Tableau.
- Tableau Calculated Fields are really just formulas that create new columns.
- You can’t create calculated fields with the same name as an existing column, meaning that you will need to create new columns for each one that contains null values.
- Tableau has a specific formula to replace null values in numeric fields, but not string (text) fields.
With that in mind, here are some formulas and methods that will help work with null values in Tableau.
How to Create Calculated Fields in Tableau
When working with null values, we recommend creating calculated fields from the Data Source view. This makes it easier to view the before and after to make sure that your formula is calculating correctly. To create a new calculated field, right click on an existing column and select “Create Calculated Field”
This will launch the Tableau formula editor. Enter in the name of the new column for the calculated field in the top box. Then enter the formula in the primary formula box.
Existing field names are referenced with square brackets around them.
Tableau will also auto-complete field names and formulas as you’re typing when you press Tab.
If the formula syntax is correct, and there are no data type mis-matches, the formula editor will say “The calculation is valid.”
Replace Nulls with an ZN formula in Tableau
When working with numeric columns, the ZN function automatically replaces null values with a 0.
Replace Nulls with an IFNULL formula in Tableau
For more control over replacing Null values, and when working with string columns, try using the IFNULL formula instead of the ZN formula in Tableau. The example below returns the same results as the ZN formula from above.
And here’s what it looks like typed into Tableau.
Note: When defining the new value in Tableau, numbers are typed in as numbers, and text is typed in with single quotes around it.
Replacing Null text with Blanks in Tableau
To replace null values with blanks in Tableau, use the IFNULL formula to check a text column, and instead of returning a specific value, tell Tableau to return two single quotes with nothing in-between them. Tableau defines text values in formulas when surrounded by single quotes, when nothing is between them it returns a blank.
The following example shows a new column being created with a calculated field that replaces nulls with blanks.
Blank values can also be referenced as part of an IF statement if you need to replace blanks in the future.
Deleting/Renaming Fields in Data Sources
When working with calculated fields to replace nulls, it may be tempting to replace the nulls, create a new calculated field, then delete the original column to make it easier to work with the data when creating visuals. We would caution Tableau developers against this, especially if a column name is already being referenced across multiple calculations and assigned to visuals.
If you delete or rename a field, any dashboards/calculated fields that rely on that field will break. Tableau will show an error saying it can’t find the specified field.
Replacing Null Values at the Data Source Level
If you are constantly having to replace null values in datasets, it may be a good idea to look into updating the original data. If data comes from a data warehouse, it may be a common issue that can be updated by the team that oversees your company’s databases and datawarehouses.
Null values can also be a sign that data isn’t flowing through from data entry to your data source as expected and could be part of a bigger issue that can be addressed upstream.
In both cases, these are typically long-term fixes, but taking the time to attempt to fix them should save an organization time if they have many reports and multiple Tableau Developers creating reports on a regular basis.
In the data grid, find any null values and overwrite them by entering a new value or leaving it blank. Then click “OK” and the nulls will be replaced throughout your Tableau workbook.
Replacing null values in Tableau can be done quickly using calculated fields. The ZN formula is the fastest method when working with numeric columns, and the IFNULL formula can quickly update text fields. For even more control over replacing nulls, you can use a regular IF statement to check IF ISNULL then replace it with a specific character or perform another action.
When creating calculated fields, they cannot be the same name as an existing column. You will need to create new columns with names that are different than the original column. While you can rename and delete the old columns, we don’t recommend it because the method is prone to error and can be difficult to troubleshoot and fix if something breaks.