The process of combining columns in Tableau is quick and easy. Columns can be combined or concatenated using formulas similar to the formulas you would use in Microsoft Excel but with a few key distinctions. We’ll highlight how to combine multiple columns and even add in custom text and handle null values to avoid errors when necessary.
Column combination or concatenation in Tableau is the process of merging two or more columns to create a new field. Tableau uses its own formula language that’s different than common spreadsheet programs like Excel, but should be familiar if you’ve used tools that are more database centric.
We’ll explain the formulas, along with additional considerations to avoid common errors.
Let’s jump in!
Table of Contents
Use Cases for Combining Columns
The technique for combining columns in Tableau is fairly simple once you get the hang of the Tableau formula syntax, but we wanted to first address when you would want to combine columns in Tableau vs. making adjustments in the incoming dataset.
By far, the most common use cases for combining multiple columns are:
- Creating a full name field from separate first and last name columns.
- Merging address components into a single location field.
- Combining date and time fields for detailed time analysis.
By combining the datasets, it becomes easier to add into various visuals that Tableau will auto-detect full addresses or time-series for charts at graphs.
Whenever possible, you should do as much data transformation as possible in Tableau. While it’s easy enough to open a spreadsheet and adjust incoming data, you will miss out on a lot of the data automation time saving benefits that you get with Tableau. Tableau lets you generate repeatable steps and apply them time and time again without any manual intervention as long as the incoming dataset doesn’t change.
How to Combine Columns in Tableau
To combine columns in Tableau, import your data and then create a new formula either from the Data Source view or Visualization view by right clicking and selecting Create Calculated Field. Then apply the formula [Column1] + [Column2]. The contents of the two columns will be concatenated as long as both fields are assigned the String data type.
Let’s break it down into individual steps.
Step 1 – Create a Calculated Field
Tableau uses the terminology, Create Calculated Field when adding a column or a formula. You can create one either by right clicking on a column name in the data preview section of the Data Sources tab, or you can create a calculated field by right clicking on a table or field name in the Data view while creating a visualization.
Example of creating a calculated field from the data sources page:
Example of creating a calculated field from the visualization page:
Either way brings you to the same screen. Our preference is to use the Data Source view though, so you can actually see a preview of the column data being merged together. Otherwise, it’s really easy to have extra spaces, no spaces, or other errors.
Step 2 – Write the Concatenation Formula
To concatenate or merge values in Tableau, reference [Column1] + [Column2]. The contents of each column will be combined. Tableau designates column names by surrounding them in square brackets [ ] and the syntax to add two text columns together is a + sign. You can concatenate additional text by surrounding it in double quotation marks in between plus signs.
The following example is combining a category and sub-category column with a – in the middle.
Notice how the – is surrounded by quote marks to designate that it should be treated as text.
Step 3 – Validate the Formula Results
The results of the formula will look like the screenshot below. We combined two columns with a dash in the middle of it. However, the results look a little bit weird in this scenario because the dash could use extra spaces around it to make the combined column easier to read once it’s added into a visual.
Note: Concatenating and merging columns is easier when using the Data Source view. It helps you make sure that the data is in the desired format and can show you if you need to add or delete additional spacing between columns or if there are errors due to data type mismatches.
Limits to the number of columns you can combine in Tableau.
There is no technical limit to the number of columns you can combine in Tableau with a single formula, however formulas can become excessively long and hard for others to understand. It can also be hard to understand if you have to personally go back after several months and make changes.
If you have a large number of columns to combine in Tableau, we recommend either using Tableau Prep or breaking down your column consolidation into several steps. For example, combine three columns at a time into two separate columns.
Then combine those two columns.
The following video from Tableau provides a brief overview of the Tableau Prep interface. It’s typically included with Tableau Creator licenses.
Tableau Prep adds some powerful data transformation features that are not available in Tableau Desktop which are designed for people who are doing heavy data manipulation prior to importing data into Tableau Desktop for visualization.
How to Fix Can’t Add String and Integer Values Errors in Tableau
One of the biggest challenges with combining columns in Tableau are data type mismatches. Because Tableau formulas use the + sign to concatenate as well as add numbers together Tableau can get confused. It determines the appropriate method to combine columns based on the assigned data type.
The following example shows a calculation error at the bottom of the page. If you click into the error, you’ll see that Tableau “Can’t Add String and Integer Values”
The error is telling you that one column is defined as text (string) and the other is defined as a number (integer)
Data types can be seen in the preview section of the data sources view and are designated by a symbol at the top of each column next to the field name.
Click on one of the symbols to show a drop down menu. Adjust the Number field to a String (text) field.
Once the data types match as Strings, you can concatenate them. Be careful of the order of operations, if applicable. If you need to perform math on these values, you will not be able to do so after converting them to strings.
Combine columns of different data types in Tableau
To combine columns with different data types in Tableau, surround a numeric column with the STR([Column1] Formula. This formula temporarily assesses the specified column as a string or text and lets you concatenate it with other string fields. Alternatively, you can adjust the entire column data type prior to applying the concatenate formula.
Combining Columns with Null Values in Tableau
Q: How do I handle null values when combining columns?
A: You can use the IFNULL() function in your calculation to handle null values effectively.
Understanding the Tableau Formula Syntax Compared to Excel
Since there are many Excel users that make the transition to Tableau, we want to highlight how the formulas are similar but different. In Excel, you concatenate columns by referencing specific cells with an & symbol in between the two values.
In Tableau values are combined using the + symbol. Rather than referencing individual cell values, columns are referenced based on the column name surrounded by [ ] square brackets. The formula is applied to all values in the columns and does not require to be filled down.
The one similarity is that text that is manually typed in has to be surrounded by double quotation marks to define it as text.
Best Practices for Combining Columns in Tableau
When combining columns in Tableau there are a few best practices to keep in mind to avoid having to correct errors or from having to wait for excessively long load times.
- Use String Data Type – Columns must be defined with a string (text) data type either at the column level or using an STR() function in the formula to properly combine.
- Concatenate in the Data Source View – Concatenating in the data source view lets you validate the consolidation is happening as planned even though the field calculation can be made in the report builder.
- Concatenate in Database or SQL for Large Datasets – If you are working with a large number of rows from a SQL database it can be quicker to concatenate fields in SQL vs using Tableau Formulas.
- Give Columns Simple Names – Don’t forget to give yourself a descriptive name of the new concatenated field. The field name is what is added to report visuals.
By following these quick tips, you should be able to avoid errors and efficiently combine columns.
Conclusion
Business intelligence analysts can quickly combine multiple columns in Tableau using the + symbol as part of a field calculation to designate the concatenation of two text fields. The formula is the same as adding two columns together. The designation between numeric and string fields will define whether you are able to concatenate, add, or result in an error when the data types mismatch between text and number.