An important step in exploratory data analysis and cleaning is to remove null values. Null values can stem from missing raw data, errors in the data type source, or calculations resulting in no value.
Tableau has several methods for handling and removing null values.
You can remove null values in Tableau by using filters. They allow you to exclude nulls for a specific column by right-clicking on the null value in a worksheet view and choosing “Exclude.” Alternatively, you can navigate to the filter shelf, drag the field, and deselect the null checkbox. You can also use functions like ZN() or IFNULL(), which can replace null with zeroes or other values.
In this article, we’ll have a detailed discussion on removing null values in Tableau. We’ll explore different methods with examples to help you better understand the concepts.
Let’s get started!
What Are Null Values?
Before you start removing null values from data in Tableau, you need to have a solid understanding of null values.
In Tableau, null values represent the absence of data in a field or column. They are not zero or blank; rather, they indicate that no data exists for a specific data point.
Null values can impact the accuracy and visual clarity of your data visualizations, so it’s often important to address them through filtering, replacement, or other methods.
How to Identify Null Values in Tableau
To identify null values in your view, first, examine the marks in your visualization.
Tableau Desktop displays null values as a small indicator, typically in the form of a small “null” label, blank spaces, or gaps in your graphs.
You can also identify null values in the tooltip when hovering over a mark in your visualization.
Here are some common situations where you might encounter null values in Tableau:
- Missing Data: Tableau interprets it as a null value if there’s no entry for a particular field in the data source or database.
- Data Blending: A null value might appear when you combine data from multiple data sources without corresponding data for a specific field between the sources.
- Calculations: Tableau might generate a null result if a calculation involves dividing by zero or any other invalid operation.
Once you have identified the null values in your data, you have several options to handle them.
4 Methods to Remove Null Values in Tableau
In this section, we’ll look at 4 methods to remove null values in Tableau.
Specifically, we’ll go over the following:
- Filter Out Null Values
- Replace Null Values With Zeros
- Format Null Values
- Using Calculated Fields to Remove Null Values
1. Filter Out Null Values
To filter out null values, click the null indicator in your visualization and select the ‘Filter Data’ option.
With this action, the null values will be removed from your view, ensuring a more accurate representation of your data.
The above action is for hiding null values from your visualizations.
2. Replace Null Values With Zeros
Another option is to replace null values with zeros or existing data using Tableau’s ZN function.
This technique is beneficial when you need to include an entry in your analysis but want to avoid erroneous calculations due to missing information.
To apply the ZN function, simply wrap your measure with the ZN function in your calculation—e.g., ZN(SUM([Sales])). This formula will automatically replace any null values with zeros.
3. Format Null Values
For continuous measures in visualizations like line charts, you can format the null values to be hidden.
Right-click on the measure pill you wish to format and select ‘Format.’
Make sure “Show at Default Position” is not selected, and the null values won’t appear in your chart.
4. Using Calculated Fields to Remove Null Values
You can also use calculated fields to handle and remove null values.
For handling null values through the calculated field, right-click on a data field in the Data pane and select “Create Calculated Field.”
You can use the following two functions to handle null values:
- IFNULL
- ZN
1. Using IFNULL to Remove Null Values
One method to remove null values is by using the IFNULL function.
This function checks if a value is null and replaces it with a specified value.
Here’s an example of using IFNULL to replace null values with 0:
IFNULL([your_field], 0)The demonstration is shown below:
The above calculation will hide null values in the profit column by replacing them with 1.
2. Using ZN to Remove Null Values
Another function to consider is the ZN function, which operates similarly to IFNULL but specifically targets null numerical values and replaces them with 0.
Its usage is as follows:
ZN([your_field])The demonstration is shown below:
This will replace null values with 0’s.
If you’d like to explore the future of data tech, check out the following video:
Final Thoughts
You’ve learned different methods to remove null values in Tableau throughout the article.
With this knowledge, you’re now well-equipped to handle missing data in your datasets and improve the overall quality and clarity of your visualizations.
By choosing the right approach for your specific project, you can ensure that your underlying data is represented clearly and understandably.
Frequently Asked Questions
In this section, you’ll find some frequently asked questions you may have when removing null values in Tableau.
How can I replace null values with zeroes in Tableau?
You can replace null values with zeroes using the ZN function. When you encounter a null value in your measure, simply wrap it with the ZN function.
For example, instead of just using [Amount], use ZN([Amount]). This ensures that null values will be converted to zeros in your visualizations and calculations.
What’s the best way to filter out null values in Tableau?
To filter out null values in Tableau, you can apply a filter to your dimension or measure.
Simply right-click on the field, choose “Filter”, and select “Non-Null Values” to exclude all records with null values.
This will remove null values from your view and exclude them from calculations used in the visualization.
How do I hide columns with null values in Tableau?
One way to hide columns with null values in a Tableau view is to use the formatting options. Right-click on your measure pill and choose “Format”.
In the Format window, navigate to the “Pane” tab and locate the “Special Values” section. Choose “Hide” to remove the display of columns with null values.
Another approach is to apply a filter to your measure or dimension, excluding null values, as described in the previous question.
How to exclude null values from a bar chart in Tableau?
To exclude null values from a bar chart, simply apply a filter to the appropriate field.
Right-click on your dimension or measure, choose “Filter”, and select “Non-Null Values”. This will remove the bars associated with null values and exclude them from your view.
Can I replace null values with custom text in Tableau?
Yes, you can replace null values with custom text in Tableau.
You can accomplish this using the IFNULL function. For example, if you want to replace null values in the [Category] field with the text “Unknown”, you can create a calculated field as follows:
IFNULL([Category], "Unknown")Then, use this calculated field in your visualizations instead of the original [Category] field.
Is it possible to include null values in Tableau filters?
Yes, it is possible to include null values in Tableau filters. When configuring your filter, you can choose to include all values, non-null values, or null values only.
By selecting the appropriate option, you can control how null values are treated in your view and calculations.
