Often, data analysts may find themselves looking to replace blank cells in their pivot table with zeroes. In most cases, it is good practice to substitute a 0 in cells with no data to keep all data values in your pivot table consistent and account for calculations where empty cells are counted. These calculations generally include averages, moving/cumulative averages, and other use cases in statistical analysis.
Assume we have a pivot table where there is no Revenue data for certain Product Types in the specific timeframe.
To replace blank cells with zeroes, nulls, or a value of your choice, one approach is to transform the source table of your pivot such that there is a row for every possible permutation of your pivot row and column labels.
Step 1: First, modify the source table of your pivot so that it shares the same grouping configurations as the underlying table representing your pivot. As seen in my example below, the Arts & Entertainment and Photography Product Types have data available for different months.
Step 2: Create a child table using the source table of your pivot and group the table by your Pivot Row field (Store Region) for a distinct list of regions. Delete all other columns in the child table.
Duplicate the child table N number of times to create N number of tables, each with a distinct list of your Pivot Row and Pivot Column fields.
There are three child tables, one for Store Region, Product Type, and Month.
Step 3: Create a new table element using the Join option. Select the Pivot Row(s) and Column as the sources. In the case of more than one Pivot Row/Column, a nested cross join is necessary.
In this example, Store Region, Product Type, and Month are selected as sources. Change the Join Keys to the number 1 (via the “Add a formula” option).
This join will output a row for each permutation of your Pivot Row and Column labels. The product of the join is commonly referred to as a “Product Table” since # of rows = Table A * Table B.
Step 4: Add a new Lookup column in the “Product Table” using the table from Step 1) as its source to bring in the Pivot Value field (Revenue). Then, wrap the Lookup() formula that gets automatically added into the formula bar (when using the Add column via lookup feature) with a Coalesce() or Zn() function.
Step 5: Finally, create a child pivot table element using the resulting table from Step 4) as the source.
And voilĂ ! As seen in the screenshot below, all empty cells are replaced with 0s.
Please share if you have any questions or if any other possible scenarios come to mind. Happy Datalyzing!