How to… create new rows
In Tableau Prep Builder version 2021.3, a new superstep has been added to allow you to create additional rows in your data that don’t currently exist. Whether you are missing rows due to having gaps between dates or want to have a row per product rather than having aggregated data, create New Rows will help you do that. You will find New Rows as an option when clicking on the ‘+’ at the end of any step meaning you can add this functionality throughout your workflow.
Using New Rows
When you add New Rows to your flow, you will first be presented with the option to pick what field you will add your new rows based on. You can pick either numerical or date fields to add new rows by. You will also have the chance to pick whether you are adding new rows based on ‘Values from one field’ or ‘Value ranges from two fields’. Depending on the structure of your data will change which option you select. If you are missing rows based on logic in a single field, then select ‘values from one field’. If your additional rows need to be created from the variance between two fields, then you will need to select the other radio button option instead.
In this example, the Allchains bike stores have been raising money for charity. The team want to monitor the total they’ve raised on average each day but the data set is missing a number of dates. Therefore, Date will be selected to allow Prep to determine what extra dates are to be added in.
Once you’ve selected the field you want to add the rows by, you will be presented with a number of other options. Let’s have a look at each in turn:
Start Value / End Value
When adding new rows, you may not want to assess the whole column or range of values within it. The option to set a minimum and maximum value to assess by is what you would choose. When you untick ‘Use minimum / maximum’ you will have the opportunity to set the range of values you want to create the new rows for.
Where do you want to add the new rows?
When adding new rows, this option will determine whether you update the data field that you are assessing or creating a new data field to show the output of the logic.
Specify your increment value
When using a date field to form your new rows, you will be asked what date increments you want to add additional by. Increments available are:
Day
Week
Month
What values should your new rows have?
With this option you are selecting what values will appear in the data set for your new rows. Your choices are:
Null - a null is added for each new row of data for any data fields in the data set.
Null or zero - zeroes are added for numerical data fields.
Copy from previous row - the value from the row in the original data set before the newly created row will be added as the value for the new row. This continues until another row from the original data source has a different value.
Daily Charity Fund Raising
Let’s return to Allchains’ charity fund raising data set. If the team want to work out the average daily amount raised for each day, then the New Rows step needs to be configured using:
Date - use the field called Date to allow us to create an additional date for any dates not featured in the data set
Update existing field - as we have some of the dates in the data set we need, it will be easier to just add in those that don’t exist at the moment.
1 day increments - we want each date between the start and the end of the data set
Copy from previous rows - this will pick up the total raised by that date from the previous date in the original data set
The result of this configuration is shown in the image below. There are some key parts to this screenshot that demonstrates what this step is doing:
Left pane - the pane is split into two data fields, Before and After. The Before column shows the values that were originally in the data set. The After column shows all the values that will be in the data set after the step.
Top right pane - like most steps in Prep Builder, the profile pane shows the profile of the resulting data set. This is useful to see how many of each value has been created.
Bottom left pane - the detail pane shows the resulting row-by-row data set.
Taking the output from the New Rows step and applying a few calculations allows us to perform the analysis we need to understand the average fundraising per day.
The resulting data set allows us to see how much has been received based on a daily run rate. This will help Allchains understand when they should close the fund raising for the charity.
Summary
Creating new rows will give you a lot of flexibility to fill gaps within your data set and avoid complex workarounds. There are many more uses for the step than what I’ve gone into here but will dig into those in future posts and Preppin’ Data challenges.
