How to Remove Duplicates in Tableau Prep

After connecting the data, drag the table onto the canvas. Add a Clean step by clicking the plus (+) icon next to it in the Flow pane. You’ll see a data preview and you can find the Identify Duplicate Rows button in the toolbar above the table preview.

Clicking Identify Duplicate Rows automatically generates a calculated field called ‘Is Duplicate Row’ that identifies the copies. It will show up in the Changes pane where you can track your actions. If not visible, expand the Changes pane by clicking the arrow to the left of the table preview.

Removing duplicate rows from the data

The ‘Is Duplicate Row’ calculation returns ‘Unique’ for just one copy of the row. 

This means that filtering out the records flagged as duplicates will keep a single copy of each record, resolving the duplication issue. To create this filter, simply select the Duplicate value from the Is Duplicate Row field and then select Exclude.

Customizing the “Is Duplicate Row?” calculation

So, what if your duplicate rows aren’t exact duplicates? Let’s add the automatically generated Source Row to this data source. To add it, click the table in your Flow pane and, in the Field Preview, double-click on Source Row and choose Include Field.

This time, clicking Identify Duplicate Rows doesn’t find any copied records because each record has a distinct Source Row value.

Instead, we can select Order ID, Product ID, and Quantity before clicking Identify Duplicate Rows. This will generate a version of the Is Duplicate Rows calculation that ignores the unselected rows. This version will ignore the Source Row column and find the duplicate rows.

Alternatively, we can edit the calculated field directly by double-clicking it in the Changes pane. Deleting Source Row from the Partition clause makes the calculation ignore it and find duplicate rows as if that field did not exist. 

If you need to keep the earliest or most recent record, you can control which row is flagged as unique by modifying the ORDERBY clause. To do this, change the field after ORDERBY and choose DESC or ASC to specify the sort order.

While it was possible to perform these tasks manually before the Identify Duplicate Rows feature, the new feature makes it a lot easier! I hope this tutorial has helped you understand how it works and the possibilities it unlocks in Tableau Prep.

Thanks for reading!
Felicia