How to Expand a Set of Date Based Data Rows to Add Missing Dates

Goal

This article describes how to enhance a Date based data set to fill in rows for "missing" days. 

For example, suppose that a data set looks like this:

2001-01-01, 3 
2001-01-02, 1
2001-01-04, 2
2001-01-11, 3

But the intended data set looks like this: 

2001-01-01, 3 
2001-01-02, 1
2001-01-03, 0
2001-01-04, 3
2001-01-05, 0
2001-01-06, 0
2001-01-07, 0
2001-01-08, 0
2001-01-09, 0
2001-01-10, 0
2001-01-11, 3

Learn

To accomplish this, start by using the EXPAND_DATE_RANGE function to build out a complete list of dates between the minimum and maximum dates that you want to graph. (If the minimum and maximum dates are dynamic, the GROUPMIN and GROUPMAX functions may be used to calculate the range at run-time.)

Once a worksheet contains the full range of desired dates, one can follow the steps in the Joining Data documentation page to perform a 'Left Outer Join' on these two sheets. The Left data is the Expanded Date Range data, the Right data is the original data set. This will produce a set of data that has null (appear as blank) values for the missing dates:

2001-01-01, 3 
2001-01-02, 1
2001-01-03, 
2001-01-04, 3
2001-01-05, 
2001-01-06, 
2001-01-07, 
2001-01-08, 
2001-01-09, 
2001-01-10, 
2001-01-11, 3

Lastly, one may manipulate these nulls by using the COALESCE function to select the values from the original sheet and then replace the nulls with a default value such as '0'.