The add new rows step in Tableau Prep is a newer step to the software arriving in the 2021.3 version. Whereas previously users would use a more complicating scaffold technique; now users can use the add rows step, but only for numerical and date formats. Check out Karina's blog for a walk-through of the numbers use-case. This blog focuses on a use-case with dates.
Below is data from the Preppin Data 2021 Wk37 challenge:
The use case here is a scenario where perhaps we want to reshape the data to have a cumulative pay tracker for each employee over the duration of the contract. To do this we first need to use calculated fields to identify the end date of the contract.
DATEADD("month", [Contract Length (months)]-1,
[Start Date])
The "-1" in the code might be a bit confusing. Effectively it ensures that we count the first month as part of the contract.
Now we use the add row step, with a breakdown of what option is doing what.
The easiest way to explain this is to take the first instance. For the Carl instance the range of values that we want to add rows for is, in 1 month intervals, from his start date of 13th December 2018 to his end date in November 2020. So we switch the value increment to 1 month. But if the case was a weekly payment structure we would select week.
These new rows with the new dates represent a new field (neither the start nor end date) and therefore we name this new field Payment Date in the field name section.
The final setting controls how to fill the columns in the new rows. We could fill in the blanks with zeros for numerical values but that makes little sense in this case as the monthly cost is not changing. Nulls is a safe option in the sense it is unlikely to cause issues, but in this case copy from previous row is useful as it sets up future investigation into cumulative pay.
When new start dates and end dates are encountered rows are added under the same logic. For Jonathan Tableau recognizes the need for 6 instances equating to the 6 months of his contract. Copying from the record above is going to ensure that all the instances that refer to the Jonathan contract have his name and monthly rate.
We can think of this as Tableau Prep working down the instances and adding in the instances required for the different start and end dates, whilst copying the other information from the original instance it is reading.
What makes this step effective is that if the data source is scheduled to refresh weekly and a new employee comes onto the payroll the flow will automatically add the instances in for the duration of the contract of the new employee.
Overall, the add rows step makes reshaping data more convenient than bringing in a scaffolding dataframe and using a join. Although scaffolding data remains relevant for string variables.