...

Ideally, your source systems are configured to capture and deliver data using a consistent set of units in a standardized structure and format. In practice, data from multiple systems can illuminate differences in the level of precision used in numeric data or differences in text entries that reference the same thing. Within

Numeric precision

In 

Suppose you apply the following transformation:

The expected output in the MySum column: -3101160.56

The actual output for in the MySum column: -3101160.5599999996

Depending on your precision requirements, you can manage precision across your columns using a transformation like the following, which rounds off MySum to three digits:

For more information on floating point computations, see https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel.

Standardize decimal precision

...

1. From the column menu, select Column Details.

2. .In the Column Details panel, select the Patterns tab.Among the patterns, select the following:

   Code Block
   {digit}.{digit}

3. In the Suggestions panel on the right, locate the set transform suggestion Edit Column transformation suggestion that uses the ROUND function. Click Edit.
4. Change the second parameter of the ROUND function to match the number of digits of precision.

You can generalize this formatting across multiple columns by applying the $col reference in the transformtransformation's functions function, as in the following:

Standardize units

...

1. In the data grid, select an instance of " kg". Note that the space should be selected, too.
2. Among the suggestion cards, select the Replace card. 
3. It should automatically choose to replace with nothing, effectively deleting the content. To check, click Modify. 

4. The transform transformation should look like the following:
   

   d-

   code

   hideNote	true

   replace col: Weight_kg on: ` kg` with: '' global: true

   trans
   p03Value '' Type step p01Name Column p01Value Weight_kg p02Name Find p02Value ' kg' p03Name Replace with p04Value true p04Name Match all occurrences SearchTerm Replace text or patterns

5. Add it to your recipe.
6. Verify that the column's data type has been changed to Integer or Decimal, depending on the values in it.

...

To normalize to English units, the first issue is easily corrected by multiplying the Weight values by 2.2, since 1 kg = 2.2 lb:

...

...

If you want to round the value to the nearest integer, use the following:

...

...

After the above is added to the recipe, you should rename the column: Weight_lbs.

...

1. Select the first quote mark in one of the entries. 
2. In the suggestion cards, select the Replace card.

3. Select the variant that deletes all quotes in the column. 

4. The full command should look like the following:
   

   d-

   code

   hideNote	true

   replace col: Height_ft on: `"` with: '' global: true

   trans
   p03Value '' Type step p01Name Column p01Value Height_ft p02Name Find p02Value `"` p03Name Replace with p04Value true p04Name Match all occurrences SearchTerm Replace text or patterns

5. Add it to your recipe.
6. The remaining steps compute the number of inches. Multiply the feet by 12, and then add the number of inches, using new columns of data.
7. Select the single quote mark, and choose the Split suggestion card. This transform transformation step should split the column into two columns: Height_ft1 and Height_ft2. 

8. Derive the value in inches:
   

   d-

   code

   hideNote	true

   derive type:single value:You can drop

   trans
   p03Value Height_in Type step p01Name Formula type p01Value Single row formula p02Name Formula p02Value ((Height_ft1 *12)+Height_ft2)

9. Add it to your recipe.
10. Rename the new column: Height_in.

11. p03Name New column name SearchTerm New formula

12. You can delete the other, interim columns.

...

If you have inconsistent units within a column, it might be possible to correct these values by applying a multiple. For example, you might be able to determine that some values are in kilometers, instead of meters, based on their much smaller values. Multiplying the kilometer values by 1000 should standardize your units. The following multiplies all values in the column Distance that are less than 1000 by 1000. 

...

...

...

...

Note the implied assumption that there are no distances in kilometers that are over 1000. 

...

You can use the following transforms transformations to perform rounding functions within these columns:

...

...

...

...

...

For more information, see Pivot Transform.Data.