With the Coalesce columns function, you can easily retrieve the first non null value across different columns to consolidate their data into a new column.
In this example, you are working on a dataset that contains information about your customers, including the different phone numbers at which you can reach them: office, mobile or home number. You can notice that some values are empty. Because you do not necessarily need to have 3 columns with similar information in your dataset, you can choose to create a new one containing only one phone number for each row, and make sure that the empty values are replaced with usable information at the same time. Here, you want to prioritize the mobile phone numbers, and retrieve the office or home number as backup.
To prioritize the mobile phone number over the other numbers,
drag the phone_mobile column header and
drop it left of the phone_office
The columns are now ordered as following:
- Use Ctrl + click or Shift + click to select the headers of the three columns containing phone numbers.
- In the Functions panel, type Coalesce columns and click the result to display the options of the associated function.
In the Direction
drop-down list, select Left to
With the column order that you have set up, the function will start by reading the phone_mobile column, and will replace any empty value by the next non empty on its right.
- Leave the default setting for the Values containing only non-printing characters drop-down list, namely Consider empty.
- Click Submit.