Show or Hide Missing Values or Empty Rows and Columns
When you’re working with dates or numeric bins, Tableau only shows the values that are represented in your data. If your data does not contain the complete range of values, the missing values will not be shown.
Consider the following data set. It contains a column for Month and a column for Size. There are rows for January through to May and September through to December, with no data recorded for June, July or August. If you create a line chart in Tableau, the missing months will not be shown. You can optionally show the missing months to make it clear that there was no data recorded during that time.
Show missing values from a known range
Tableau can infer the missing months of June, July and August from this example data set because there's a clearly defined possible domain, such as for dates or numeric bins. If your data contains Monday, Tuesday, Wednesday, Saturday and Sunday, Tableau can fill in the missing values as Thursday and Friday. However, if your data set was a list of colours, such as Teal, Green, Blue and Yellow, Tableau can't infer the missing values because there's no definitive way to know what the rest of the values should be.
If you don't see the option for Show Missing Values, your data doesn't meet the requirement where Tableau can determine the missing values.
Toggle missing values
By default, missing header values in a date range or numeric bins are not shown. The x-axis in this example goes from May to September.
You can display the missing header values to indicate incomplete data.
Right-click (Control-click on Mac) the date or bin headers and select Show Missing Values.
The x-axis in this example updates to include headers for June, July and August.
To go back to the default behaviour, you can turn Show Missing Values back off.
- Right-click (Control-click on Mac) the date or bin headers and untick Show Missing Values.
Null vs Missing
There's a difference between missing header values and null data. In the example of the missing months, June, July and August are missing from the data set as entire rows. March is present as a row in the data, but the value for the Size column for March is empty. This is a null value.
In a viz, there is a null indicator only for nulls, not for gaps due to missing header values.
You can replace null values with a calculated field using the ZN() function. ZN replaces nulls (but not missing header values) with zeros. Size for March is null, so ZN(Size)
would put a zero for March. However, there are no rows at all for June, July or August. ZN would not create rows or add zeros for those months as they are missing rather than null.
See Number Functions for details about the ZN function, or Format null values for more information about how to handle nulls.
Note: You can also perform calculations on missing values that are shown in the view. To do this, open the Analysis menu at the top, and then select Infer Properties from Missing Values. For an example of this, see Predictive Modelling with Generated Marks.
Show and Hide Empty Rows and Columns
When you are working with fields that are not dates or numeric bins, Tableau hides empty rows and columns by default.
For example, imagine you're looking at student clubs and the students who are in those clubs. If there's a student who isn't in a club, that student's name won't appear in a dimension-only viz of student and club. You can show empty rows by selecting Analysis > Table Layout > Show Empty Rows.
Empty Rows Hidden (default) Jay isn't in any clubs, so there's no row for the student Jay. Every row has a placeholder mark (Abc). | Empty Rows Shown Jay isn't in any clubs, but empty rows are shown, so there is a row for Jay with no placeholder mark (Abc). |
Similarly, show any empty columns by selecting Analysis > Table Layout > Show Empty Columns.
Note: If you are working with multi-fact relationships in data sources with multiple base tables, there may be more complex logic that determines when nulls are seen in empty rows. For more information, see About Multi-fact Relationship Data Models.