The Concatenate transform allows you to merge two or more columns of data (as well as free text), to create a new column of data. Common use cases include merging first name and last name to get full name or merging two id columns to get a unique id.
Creating a Concatenated Column
To create a concatenated column using the Concatenate Transform, click on the "+Add Transforms" button then select "Concatenate" from the list of transforms under the "Clean & Prepare" section.
Once you are in the Concatenate working area, there are 3 parts you'll need to set: Column Title, Concatenation Expression, and the "Skip rows with empty cells" checkbox.
Since you'll be creating a new column of data with the Concatenate transform, you'll need to give the column you're creating a name by filling in the "Column Title" field.
The "Concatenation Expression" field is where you will craft how you want your new column to look. Within the concatenation expression you can use the following to create your new column:
- Columns of data
- Free text
We've made it as simple as possible for you to tell Grow how you want the output data to look. Simply type the output value you desire. Purple text wrapped in brackets represent column values, and will change based on your data. Black text will appear as is. No need to join columns or text together with + or & like you would using SQL or formulas within spreadsheet apps.
*note: the only character limitation in the "Concatenation Expression" is trying to concatenate a column if the title of the column contains brackets. If you have brackets in your column name, please use the Rename Column transform before using the Concatenate transform to remove those brackets.
Skip Rows with Empty Cells
The last thing you need to decide before you hit run is whether you want Grow to skip rows in the event you have empty cell(s) in any column you've specified in the "Concatenation Expression".
When “Skip rows with empty cells” is not selected (default), output value will be a concatenation of all cells referenced in the "Concatenation Expression" regardless of whether they are empty or filled.
When “Skip rows with empty cells” is selected: Output value will be blank if any column involved in the concatenation contains an empty cell.
See image below for an example of of the difference between each selection.
After you've added a column title, created your "Concatenation Expression", and decided whether you will Skip rows with empty cells, you can Run your transform and find your newly created column.
If you had the source columns that were first_name and last_name but you wanted to have a full name column as well you may choose a "Column Title" like full_name.
In the "Concatenation Expression" you would type: [first_name] [last_name]
Or perhaps you'd like to include some free text in the output as well. In that case you might put something like "Hi, my name is [first_name] [last_name]"
Lastly you've decided it doesn't make sense to have data in the full_name field if either the first_name or last_name is missing, in this case you'd select the "Skip rows with empty cells" box so that you only get complete full names.
After you hit run, you'll see the full_name column populated.