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!*