Cleaning Data in Tableau Prep

by Hannah Laughton

How to clean a quantity field containing both values and units (different units) to have values only - all of which correspond to the same unit.

You have the following data pertaining to liquid volume:

However, the above layout is not ideal. Subsequently, you want to transform the data so that the format looks something like this:

Most of the units presented in the original data are in milliliters (mL), so it makes sense to convert the one value in Liters (L) to mL. Since this requires a conversion, you know that a calculation field is needed.

In order to do this, you need to do the following (you'll soon understand why):

  1. Duplicate Field
  2. Clean - Remove Letters
  3. Change Data Type to Number
  4. Rename Field (in this case Quantity-#)

Now you can create the following calculated field:

Where the CONTAINS function requires a string and substring and the reason for the previous steps becomes clear (multiplication).

Resulting in:

Hannah Laughton

Wed 03 Aug 2022