Filling cells from above - 2.3

Talend Data Preparation User Guide

author
Talend Documentation Team
EnrichVersion
6.5
2.3
EnrichProdName
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Real-Time Big Data Platform
task
Data Quality and Preparation > Cleansing data
EnrichPlatform
Talend Data Preparation

The Fill empty cells from above function allows you to copy the value from the nearest non-empty cell above and fill the following empty ones with this value.

When there is a series of empty cells, they all fill with the same value that was originally picked from above.

Let's say for example that you have received an Excel file containing a list of the American States, along with their state code, and the region they belong to. Instead of having the region names repeated for several rows, the person who sent this input file used merged cells to regroup them.

When you open this file in Talend Data Preparation, the merged cells will result in one correct row, followed by several empty cells, as shown in the quality bar for the Region column.

Instead, you want the information about the region to be repeated, so that each row can be self-contained.

You will use the Fill empty cells from above to reuse each region name in the empty cells located directly under them.

Procedure

  1. Click the header of the Region column to select its content.
  2. In the functions panel, type Fill empty cells from above.
  3. Point your mouse over the function name to preview its result, and click to apply it.

Results

All the empty cells have been filled with the appropriate value, based on the last non-empty one, and all the US States now have their corresponding region.