Applying a value to all cells - Cloud

Talend Cloud Data Preparation Getting Started Guide

Version
Cloud
Language
English
Product
Talend Cloud
Module
Talend Data Preparation
Content
Data Quality and Preparation > Cleansing data
Last publication date
2024-03-05

Applying a certain value to many cells at once can save you a lot of time when correcting invalid cells.

The State column is the last column containing incorrect data. This column lists the States from which the customers have rented a movie, using a two-letter code. You can notice that among all the other US state codes, the occurrences of Texas stand out as errors.

The Texas value is invalid.

Rather than simply deleting the corresponding lines with the quality bar like you did before, you are going to correct one of the invalid cells, and apply the new value to all the cells with the same error. To replace the occurrences of Texas with the correct value, proceed as follows:

Procedure

  1. In the State column, double-click one of the occurrences of Texas.
    You can now edit the content of the cell. When you delete the previous value, a drop-down list opens, suggesting all the values that are part of the US State Code dictionary-based semantic type, sorted alphabetically.
  2. Because the code for Texas is not part of the first results that are displayed, start typing T, to see which results come up.

    The suggestions will become more accurate as you type. But if the expected value can already be found from the original list, you can directly select it.

  3. From this refined list, select the state code that corresponds to Texas, namely TX.
  4. Select the check box Apply to identical values.
    Apply to identical values checkbox.
  5. Click Submit.

Results

All the occurrences of Texas have been replaced by the correct TX State code and the quality bar now indicates that all the data in the State column is correct.

Note that when the State column is selected, the data is visualized in the form of an interactive map of the United States in the Data profiling panel.

Interactive map of the United States in the Data profiling panel.