How to use the Generate Rows tool to fill in missing months

[HenrietteH]

Let's say you have a dataset with products that aren't sold very often (not every month) but you would like to create data points for every month and fill in quantity and amount as zero for reporting purposes.

This is the perfect time to use the Generate Rows tool. It will allow you to take the earliest month on the dataset, increment that by one month (generating a new row each time) until it has reached the latest month or the current month you are in.

To do so, first use a Summarize Tool to identify both the min(earliest) and max(most recent)dates on your data.

In this example, we created a field called [CurrentMonth] to be used as the max date, but you could certainly use a date provided on your data set.

Then, set up the Generate Rows tool as follows:

We are creating a new field called [Date] that will hold all of the date values between the earliest date on the data set and the current month.

The Initialization Expression [Min_Date] is the starting value that will be used for the first row. In our case, it is the earliest date on the data set as identified by the summarize tool.

The Loop Expression is what is being used to generate new rows. In our case, it increments [Date] by one month until the Condition Expression is met. As long as the Condition Expression returns true, another row will be created. Once the Condition Expression returns false, no more rows will be created.

Once these rows are created, you will have to join them back to your dataset and fill in any missing columns appropriately. See the attached workflow for an example of how to do that.

[NotQuiteClueless]

Thank you so much! I have spent the last working day trying without success to put missing columns into my Tableau viz. Your answer above and a few adjustments for my own requirements and I'm there!

[charity_wilson]

This was exactly what I needed.  Your work flow was well documented and walked me thru the whole process.  Thank you.

[brad_j_crep]

I have a list of sales for every quarter hour.  I'm missing times when there were no sales and would like to fill in those quarter hours if possible.  It's like in the time is 14:45 and the next is 15:15 how do I get the 15:00 in the list.  Now there can be several 'holes in time' like this throughout the document.  I've tried what you have above and altered for time but I'm not getting something right.  The Loop Expression is DateTimeAdd([TimeRow],00:15:00,"minutes") which leads to a Parse Error at char(24):Malformed Function Call.  Check it the old way and it's going slowly through 7680 records.  I have Alteryx, I don't need to do that.

Any helps appreciated.

[michael_heitz]

Genius! Made a couple modifications and it worked perfectly for a similar problem I was trying to deal with! Thanks

[Katie_K]

Thanks for this post Henriette - this just helped me solve a tricky problem, much appreciated! 😀