Prep Builder: Add a unique ROW ID

Data Preparation using Builder: Adding unique and sequential ROW ID

In this Blog I want to show how to add a unique sequential ROW ID ascending by date. I am using a Kaggle data set containing all F1 Races between 1955 and 2022. The Race ID in races.csv seems unique, however it is not sequentially ordered, which Is what I need.

Tableau Prep Builder can easily create ROW IDs to any dataset and is my first “go to” tool when working with Tableau. A unique record ID (“UID”) is important, because it identifies a record (in a data table) as unique from all other records. Subsequently it allows it to be uniquely referenced.

There are two ways to add a unique sequential ROW ID with Create Calculated Field using the function:

1. ROW_NUMBER();
2. RANK(), which supports the Visual Calculation Editor.

Both functions belong to the Prep Analytics Functions, sometimes incorectly referred to as window calculations. Prep Analytics functions make calculations across the entire data set or a selection of rows possible. Window calculations on what is visible.

First ensure your data set has no duplicates, see this blog for two methods in Tableau Prep. Once this is done continue with one of below options.

Option 1 – use ROW_NUMBER for a sequential ROW ID

This function can be used again. However, now no PARTITION is required. As we want to use the entire table. The objective is a unique row number by order date ascended. As we have established date is unique the syntax is then quite straightforward:

{ ORDERBY [date] ASC: ROW_NUMBER() }

Also multiple fields can be used to order the data before assigning a row number. For example raceId and date. Just add a comma and the field to ORDERBY (“2024, [radeId]”).

Option 2 – use RANK for a sequential ROW ID

With this function we can use the built-in visual calculation editor or add the calculation manually. The basic Syntax follows the previous option:

{ ORDERBY [date] ASC: RANK() }

As long as we have ascertained there are no duplicates above will work.

For RANK the visual calculation editor is available and would look as follows:

Conclusion

To create a new output file with a UID we have shown 2 steps.

1. One Clean step to check duplicates (see this blog), then another;
2. Clean step adding the UID and removing all fields that we do not want.

This segregation is not needed, al data prep changes can be done in one Clean step. However I prefer to segregate different steps by purpose. In this example a deduplication and then adding a UID.

The final flow with the two steps looks like this:

The output file now contains the sequential UID, which will update for any new race added to this Calendar file.

Preview the last Clean Step in Tableau or Connect the output file to visualize the data. To validate the UID I created a straight line incrementing with 1 per Race Date as expected.

I hope you enjoyed this short blog on Tableau Prep Builder use cases to create a sequential UID field in any data set.

Check my blogs for more Tableau and Tableau Prep content.

---

If you want to read other blogs about software like Tableau and Alteryx, follow a training course or book a consultant please visit The Information Lab NL for more information.

---