How to Concatenate in Tableau

Combining text fields in Tableau, a process known as concatenation, is a simple yet powerful way to make your data visualizations more descriptive, intuitive, and user-friendly. This article will guide you through the primary methods for concatenating strings in Tableau, show you how to handle common issues like different data types and null values, and provide practical examples you can immediately apply to your own dashboards.

What is Concatenation in Tableau?

Concatenation simply means joining two or more strings of text together to create a single string. In the context of a dataset, this might involve combining a 'First Name' field and a 'Last Name' field to create a 'Full Name', or merging details like 'City', 'State', and 'Zip Code' into a complete 'Address' field.

Why is this so useful in data visualization? Here are a few common reasons:

• Creating Better Labels: You can create dynamic and informative labels for your charts and graphs. Instead of a label just showing "New York," it could say "State: New York."
• Building Unique Identifiers: Sometimes you need to create a unique ID by combining existing fields, like joining an Order ID with a Product ID to get a unique line item identifier.
• Improving Tooltips: You can make your tooltips much richer by concatenating several fields into a single, cohesive descriptive sentence, providing users with more context when they hover over a data point.
• Cleaning and Formatting Data: It's a fundamental part of data preparation, allowing you to reformat fields into a more readable and consistent structure directly within Tableau.

Method 1: Using the '+' Operator for Basic Concatenation

The most straightforward way to combine strings in Tableau is by using the plus (+) operator. This works exactly as you might expect: it links one string directly to the end of another. You can use it to join fields from your data source and also to include static text (literals).

To do this, you'll need to create a calculated field:

1. In your Tableau worksheet, right-click anywhere in the 'Data' pane and select Create Calculated Field....
2. Give your new field a descriptive name, like "Full Name".
3. In the formula editor, you can start combining your fields.

Let's say you have two fields in your data: [First Name] and [Last Name]. To combine them into a single 'Full Name' field, you'd use the following formula:

[First Name] + ' ' + [Last Name]

Breaking Down the Formula:

• [First Name]: This pulls the string from the 'First Name' field.
• +: This is the concatenation operator that joins the strings.
• ' ': This is a static string, also called a string literal. Here, it’s a single space character enclosed in single quotes. Without it, the first and last names would be mashed together (e.g., "JaneDoe"). This space adds the necessary separation ("Jane Doe").
• [Last Name]: This pulls the string from the 'Last Name' field.

The Common Pitfall: Handling NULL Values

The + operator has one major weakness: if any of the fields involved in the calculation contain a NULL value, the entire result of the concatenation will be NULL. A NULL value represents missing or unknown data. If a person's first name is missing (NULL) in your dataset, the formula NULL + ' ' + 'Doe' will result in NULL, not "' Doe'". This can lead to unexpected gaps in your data.

Luckily, Tableau has an easy way to fix this using the IFNULL() function. The IFNULL() function checks if a field is NULL and, if it is, replaces it with a specified alternative value.

To make our 'Full Name' calculation more robust, we can wrap our fields in IFNULL() like this:

IFNULL([First Name],'') + ' ' + IFNULL([Last Name],'')

How the IFNULL() Solution Works:

• IFNULL([First Name],''): This checks the [First Name] field. If it finds a value (like "Jane"), it uses that value. If it finds a NULL, it replaces it with an empty string ('').
• This ensures that even if one field is empty, the calculation still runs and returns the non-empty parts, preventing your entire label from disappearing.

Method 2: Combining Strings and Numbers with the STRING() Function

You'll often run into situations where you need to combine text with other data types, like numbers or dates. For example, you might want to create a label that says "Sales for 2023: $50,000". If you tried to do this directly with the + operator, you'd run into an error.

If you have a field [Order Year] with numeric values (e.g., 2023) and a text field [Category], a formula like this will fail:

[Category] + ' - ' + [Order Year] // This will produce an error!

Tableau will throw an error because it can't add a string and a number together. To solve this, you need to explicitly convert the numeric or date field into a string. The STRING() function is built for exactly this purpose.

Here's how you'd fix the formula above:

[Category] + ' - ' + STRING([Order Year])

The STRING([Order Year]) part of the formula converts the numeric value into a text string, allowing the + operator to join everything together correctly. The output would look like "Technology - 2023".

Another Practical Example with Sales Data:

Let's say you want to create a dynamic label for a chart showing total sales. You have a measure [Sales] which is a number.

'Total Sales: ' + '$' + STRING([Sales])

If the value of [Sales] for a given data point is 1500, the formula would produce the string "Total Sales: $1500".

Putting It All Together: Real-World Scenarios

Knowing the mechanics is one thing, but seeing concatenation in action is how you really learn. Here are a few practical scenarios where these techniques are incredibly useful.

Scenario 1: Creating Descriptive Chart Tooltips

The default tooltips in Tableau are good, but calculated fields allow you to make them great. Imagine you have a bar chart showing sales by state. Instead of showing the individual fields, you can create a single calculated field called "Tooltip Label" to display a full sentence.

Fields available: [State], [Region], [Sales], [Profit]

Formula for "Tooltip Label":

'In the ' + [Region] + ' region, the state of ' + [State] + ' had total sales of $' + STRING(ROUND([Sales], 0)) + ' and a profit of $' + STRING(ROUND([Profit], 0)) + '.'

Drag this calculated field onto the 'Tooltip' mark. Now, when a user hovers over the bar for California, they'll see a clean, easy-to-read sentence like: "In the West region, the state of California had total sales of $91723 and a profit of $12044." - much more engaging than a list of raw data points!

Scenario 2: Building Full Addresses for Mapping

If your dataset contains address information split across multiple columns ([Street Address], [City], [State], [Zip Code]), you can concatenate them into a single field. This is not only helpful for display purposes but can also assist Tableau's geographic mapping capabilities.

Formula for "Full Address":

[Street Address] + ', ' + [City] + ', ' + [State] + ' ' + STRING([Zip Code])

This single calculated field, "Full Address", can then be assigned a geographic role (e.g., Address) in the Data pane, allowing Tableau to automatically plot the locations on a map.

Quick Tips and Final Reminders

• Don't Forget the Spaces: The most common mistake is forgetting to add separators. Always include a space (' '), comma (', '), or hyphen (' - ') inside quotes when joining fields to keep them readable.
• Data Types are Crucial: Remember, the + operator only works if all elements are strings. Always use STRING() to convert numbers, dates, or boolean values before you try to concatenate them.
• Proactively Handle NULLs: Get in the habit of using IFNULL([Your Field], ''). It protects your concatenated strings from disappearing when there's missing data and makes your dashboards more reliable.

Final Thoughts

Mastering string concatenation is a fundamental skill that unlocks a new level of customization and clarity in your Tableau work. By using the + operator for basic joining, STRING() to handle mixed data types, and IFNULL() to guard against missing data, you can transform raw data fields into meaningful, context-rich labels and descriptions that elevate your dashboards from good to great.

Crafting formulas and manually configuring dashboards in tools like Tableau is powerful, but it's often a time-consuming process of trial-and-error. We built Graphed to remove this friction by letting you build real-time dashboards using simple, natural language. Instead of writing calculations, you can just ask, "Show me a chart of total sales by state," and the dashboards are created for you in seconds, with your data sources connected and updated automatically.