In the , it is very easy to identify where there are errors in your data. What is truly innovative is how you correct them:

1. Identify missing or mismatched data by color-coded bars in column data. 
2. Select a bar. 
3. Suggestions are offered in a set of cards on the right panel.
4. Click a suggestion, and immediately see the effects of the suggested transformation previewed in the data grid. 
   1. If the transformation needs tweaking, you can edit the transformation as needed.
   2. If the transformation is not the correct one, click another suggestion.
5. When satisfied, you add the transformation, and your sample of data is transformed. 

Select errors in your data, and review AI-driven suggestions for how to correct. Make the change on the spot. 

Through this series of seeing, selecting, and refining issues in your sampled data, you can address basic errors in data mismatches, missing data, non-standard values, outlier values, and much more to improve the overall consistency and quality of your data. 

Find bad data

In the Transformer page, above each column of data is a data quality bar and histogram. 

The top bar is the data quality bar. The data quality bar segments the values found in the column into three color-coded bands:

Mismatched values in a column are indicated in red

Change column data type

In the image above, you can identify the data type of the column based on the icon to the left of the column name (POS_Sales). In this case, the data type is Decimal. 

In some cases, invalid data can be fixed by simply changing the column data type. You can click the current data type indicator to review and select a more appropriate data type.

Change column data type

Find outlier data

You can explore the details of a column of data to review statistical metrics on the data and to locate outlier values. In the column menu, select Column Details.

Column Details

Fix Mismatched Values

When  evaluates a dataset sample, it interprets the values in a column against its expectations for the values. Based on the column's specified data type and internal pattern matching, values are categorized as valid, mismatched, or missing. These value categories are represented in a slender bar at the top of each column. 

• A mismatched value is any value that seems to be of a different data type than the type specified for the column. For example, if the value San Francisco appears in a column of Zip Code type, it would be marked as a mismatched value.

In the data quality bar, mismatched values are identified in red:

Mismatched values in red

Mismatched values can be sourced from a variety of issues:

• Values may be miskeyed into the source system.
• The source system may introduce errors in output, particularly if the data is generated for export using a customized structure.
• Incorrect use of column delimiters may create offsets among fields in individual rows.
• Data may be badly structured across a set of rows.
• The column may be assigned the wrong data type.

To locate data:

• From the Transformer page, click the mismatched values in a column's data quality bar to see their count, highlight them in the rows of the data grid, and trigger a set of suggestions for your review.

• To refine the data grid view, click the Show Only Affected Rows checkbox in the status bar at the bottom of the screen. Only the rows that are affected by the previewed transform are displayed.
  
  

  Tip: This step highlights specific values that are mismatched. You can take note of individual values.

• To locate a specific value, click the Filters icon on the right side of the screen. In the Rows tab, enter the specific value to locate. Rows containing this value are highlighted. Back in the data grid, you can select one of these highlighted values to be prompted for suggestions.   

Methods for fixing mismatched data

When you discover mismatched data in your dataset, you have the following basic methods of fixing it:

1. Change the data type. If the percentage of mismatched rows is significant, you may need to change the data type for a better match. 

2. Replace the values with constant values. This method works if it is clear to you that the values should be a single, consistent value. Select the mismatched values in the column, and then select one of the highlighted mismatched values. Use the replace transform to change the mismatched values to corrected values.

   Tip: One easy way to fix isolated problems with mismatched values is to highlight a mismatched value in the data grid. A new set of suggestions is displayed. You can select the replace suggestion and then modify it to include the replacement value.

3. Set values with other columns' values. You can use the set transform to fix mismatched values by replacing them with the corresponding values from other columns.

4. Use functions. Data can be fixed by using a function in conjunction with the set transform to replace mismatched values.

5. Delete rows. Select the mismatched values and use the delete transform to remove the problematic rows.
6. Hide the column for now. You can remove the column from display if you want to focus on other things. Select Hide from the column drop-down. Note that hidden columns appear in any generated output. 
7. Delete the column. If the column data is unnecessary or otherwise unusable, you can delete the entire column from your dataset. Select Delete from the column drop-down.

Mismatched values in transform code

In your transforms, mismatched data can be identified references as in the following:

Note that the single quotes are important around the value, which identifies the value as a constant.

Trim data

To trim whitespace out of a column, use the following transformation:

The $col token is a reference to the column name to which the formula is being applied. For more information, see Source Metadata References. 

This step may increase the number of missing values (for values that contain only whitespace characters) and the number of instances of matching values (for values that have spaces before and after an alphanumeric value).

You can modify the above transformation to trim leading and trailing spaces across all columns in your dataset. The wildcard (*) applies the formula to all columns in the dataset.

You can extend the above transformation further by removing any leading or trailing single- and double-quote marks using the TRIMQUOTES function wrapped around the TRIM reference:

Set values using other columns

You can use values from other columns to replace mismatched values in your current column. Using the previous example, mismatched postal codes are replaced by the corresponding value in the parent entity's postal code column (parent_postal_code):

Use functions to fix mismatched values

In your transforms, you can insert a predefined function to replace mismatched data values. In the following example, the value for mismatched values in the score column are computed as the average of all values in the column:

Bad data typing

For columns that have a high percentage of mismatched values, the column's data type may have been mis-assigned. In the following example, a column containing data on precipitation in inches has been mis-typed as Boolean data:

Mis-typed column data type

To change a column's data type, click the type identifier at the top of the column and select a new type. In this case, you would select Decimal.

As you can see in the previous example, the precipitation column contains values set to T, which may be short for true. When the data type is set to Decimal, these values now register as mismatched data. To fix, you can replace all T values with 1.0 using the set transform.

Select an instance of T in the column and click the Set suggestion card. Click Modify. For the value in the transform, enter 1.0. Your transform should look like the following: