KNOWLEDGE BASE

Populate Empty Cells in a Text Table with Zeroes or Existing Data


Published: 27 Mar 2017
Last Modified Date: 25 Sep 2017

Question

How to populate empty cells in a text table with zeroes or existing data. 

Environment

Tableau Desktop

Answer

The below examples use Sample - Superstore data.

Option 1: Populate empty cells with zeroes 

  1. Select Analysis > Create Calculated Field.
  2. Name the field ZN Lookup, enter the following calculation, then click OK: 
    ZN(LOOKUP(SUM([Sales]),0)) 
  3. Place Category on Columns
  4. In the Dimensions pane, right-click Category and select Duplicate
  5. Place Category (copy) on Rows.
  6. Place ZN Lookup on Text. Empty cells are now populated with zeroes.

Option 2: Populate empty cells with existing column data

Follow steps 1-2 in Option 1.
  1. In a new worksheet, select Analysis > Create Calculated Field
  2. Name the field Vertical ZN, enter the following calculation, then click OK WINDOW_SUM([ZN Lookup])
  3. Place Category on Columns.
  4. Place Category (copy) on Rows.
  5. Place Vertical ZN on Text.
  6. Right-click Vertical ZN on Text and select Compute Using > Table Down.  

Option 3: Populate empty cells with existing row data

Follow the steps from Option 2, but name the field Horizontal ZN and in step 6, select Compute Using > Table Across

 

Additional Information

There are some situations where the above solution will not behave as expected. 
  • If you are using a table calculation as a filter, the nulls may be filtered out rather than replaced with zeroes.
  • If you have empty rows, rather than empty cells, in the data source, results will not be consistent. 
Did this article resolve the issue?