Fill Gaps in Sequential Data
Supported in Tableau Prep Builder version 2021.3.1 and later and on the web in Tableau Server and Tableau Cloud version 2021.3.0 and later.
When you have gaps in your sequential data set, you may need to fill those gaps with new rows to effectively analyse your data or perform trend analysis. You can use the New Rows step type to generate the missing rows and set configuration options to get the results you need.
New rows can be generated for fields with numeric (whole numbers) or date values. Configuration options include:
- Generate rows using values from a single field or two fields
- Use all data in the field or select a range of values
- Create a new field with the results or add the new rows to your existing fields
- Set the increment (up to 10,000) to use when generating the new rows
- Set the values for the new rows to zero or Null, or copy the value from the previous row.
Example 1: You have a table of sales data, but there are some days where no sales are recorded. You need a row for every day, not just the days where you had sales. With New Rows, you can generate rows for your missing days and add them to you existing field "Days of the week". Since no sales are recorded for those days, you want the quantity sold value to be zero.
Example 2: You have a table of sales data where orders filled is recorded using a range of dates. You need a row for each day. Since you don't know how many orders were filled each day, you want the values for the new rows to be Null. With New Rows, you can generate the missing rows between the two dates and create a new field called "All Days" to preserve your original data.
Generate new rows
In the Flow pane, click the plus icon, and select New Rows. A New Rows step displays in the Flow pane.
Complete the following steps to configure your options to generate the new rows.
- How do you want to add new rows? Use one of the following options to select the field or fields where rows are missing.
Values from one field: Generate missing rows from values in a single field. Use this option for Number (whole) or Date data types.
By default, use the minimum and maximum value to generate missing rows. This option uses all values in the field. If you only want to use a range of values to generate the missing rows, set a Start value and End value.
Note: The Start value and End value fields can't be used to generate rows outside of your current data set.
- Value ranges from two fields: Generate new rows using a value range between two date fields. This option is only available for Date data types, uses all values in the field and requires that both fields have the same data type.
- Where do you want to add the new rows? When using a single field, you can add the new rows to your existing field or create a new field to preserve your original data. When using value ranges from two fields, you must create a new field.
Field Name: Enter a name for the new field.
- Specify your increment value: Enter a value from 1-10,000. Each new row is incremented by the value you select. If you select a value that is greater than the gap between values, no new rows are generated.
- Number fields: Select a numeric value.
- Date fields: Select a numeric value and select Day, Week or Month.
What values should your new rows have?: Select an option to fill in the other field values for the new rows.
- Null: Populate all field values with Null.
- Null or zero: Populate all text values with Null and all numeric values with zero.
- Copy from previous row: Populate all field values with the value from the previous row.
New rows are shown in the Generated Rows pane in bold as you enter your configuration settings. The row details are shown in the New Rows Results pane.