How to Merge Two Columns in Tableau

Combining columns is a common and necessary task when you’re cleaning up your data for analysis in Tableau. Whether you need to create a full name from separate first and last name columns or build a unique ID from several different fields, Tableau provides a few straightforward ways to get it done. This guide will walk you through the most common methods, complete with step-by-step instructions and practical examples.

Why Merge Columns in Tableau?

Before jumping into the "how," it's helpful to understand the "why." Merging columns helps you prepare your data so your visualizations are more effective and easier to understand. Here are a few common scenarios where you'd need to combine fields:

• Creating Full Names: Your data probably has 'First Name' and 'Last Name' in separate columns. To display a full name on a report or chart, you'll need to merge them into a single 'Full Name' field.
• Building a Unique Identifier (ID): Sometimes, no single column can uniquely identify a row. You might need to combine an 'Order ID' with a 'Product ID' to create a unique identifier for each line item in an order.
• Hierarchical Descriptions: You can create more descriptive labels by combining categories and sub-categories. For instance, merging 'Product Category' (e.g., 'Furniture') and 'Product Name' (e.g., 'Executive Chair') into a single field like 'Furniture - Executive Chair'.
• Concatenating Location Data: You might need to combine 'City', 'State', and 'Country' columns to create a full address field for mapping visualizations or detailed reports.

In all these cases, merging columns cleans up your data pane and makes your final reports far more readable for your audience.

Method 1: Using a Calculated Field to Merge Columns

The most powerful and flexible way to merge columns in Tableau is by creating a calculated field. This method gives you total control over the output, including the separator you use and how you handle different data types.

A calculated field allows you to create a new column in your data source by applying a formula to your existing fields. For merging, we'll use a simple concatenation formula.

Step-by-Step Guide to Using a Calculated Field

Let’s walk through the most frequent example: combining a 'First Name' column and a 'Last Name' column to create a 'Full Name'.

1. Create a New Calculated Field: In the Data pane on the left side of your screen, right-click anywhere on an empty space and select "Create Calculated Field...". Alternatively, you can go to the Analysis menu at the top and choose "Create Calculated Field...".

2. Name Your New Field: A new window will pop up. In the title box at the top, give your new field a descriptive name. In this case, we'll name it "Full Name".

3. Write the Concatenation Formula: In the large formula box, you'll combine the fields you want to merge. To combine string (text) fields in Tableau, you use the plus + operator. To combine 'First Name' and 'Last Name' with a space in between, your formula will be:

[First Name] + " " + [Last Name]

Breaking Down the Formula:

• [First Name]: This references the values in your 'First Name' column. Field names in Tableau formulas are always wrapped in square brackets [ ].
• +: This is the concatenation operator. It tells Tableau to join the strings together.
• " ": This is a literal string. In this case, we're adding a space character between the names. Any text you add yourself needs to be enclosed in double quotes. If you wanted a comma separator, you would use ", " instead.
• [Last Name]: This references the values from your 'Last Name' column.

4. Apply and Close: At the bottom of the window, you'll see a small message saying "The calculation is valid." This confirms your formula has no errors. Click "OK".

You’ll now see your new 'Full Name' field in the Data pane, under the Dimensions or Measures section. You can drag and drop it into your reports just like any other field.

Merging Different Data Types (Numbers, Dates)

What if you want to merge a string field with a number or date field? For example, creating a report ID like 'Order - 1001'. If you try to directly combine them like "Order - " + [Order ID], Tableau will show an error because you can’t add a string and an integer together.

You first need to convert the number or date field into a string. You can do this using the STR() function.

Example: Merging a String and a Number

Let's say you have a field called 'Campus' (text) and 'Building Number' (a number). To create a 'Location ID', you would use the following formula in your calculated field:

[Campus] + " - " + STR([Building Number])

This formula first takes the value from the 'Campus' field (e.g., "Main"), adds a separator, and then converts the 'Building Number' (e.g., 201) into a text string ("201") before joining them. The final result would be "Main - 201".

Handling NULL Values When Merging

A common issue occurs when one of the columns you are merging contains a NULL (empty) value. If either 'First Name' or 'Last Name' is NULL, the entire output of the formula [First Name] + " " + [Last Name] will also be NULL.

To avoid this, you can use the IFNULL() function. This function checks if a field is NULL and, if it is, replaces it with a value you specify. To handle potential empty names, you can modify your 'Full Name' formula:

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

This still might result in a hanging space if one of the names is missing (e.g., " John" or "Doe "). For a cleaner version that also trims spaces, you can get a little more sophisticated:

TRIM(IFNULL([First Name], "") + " " + IFNULL([Last Name], ""))

This way, you ensure you always get a sane output, even with missing data.

Method 2: Using the 'Combine Fields' Feature

For a much quicker, though less flexible, method, you can use Tableau's built-in "Combine Fields" feature. This is best used when you want a quick way to group multiple dimensions together, create hierarchies, or use the combined field in another calculation.

Step-by-Step Guide to 'Combine Fields'

1. Select the Fields: In the Data pane, select the columns you want to merge. Hold down the Ctrl key (or Cmd on a Mac) and click on each field. For our example, click on 'First Name' and then 'Last Name'.

2. Create the Combined Field: Right-click on one of the selected fields and navigate to Create > Combined Field....

Tableau will automatically create a new field named something like "First Name & Last Name (Combined)".

What it does: This new field concatenates the values from the selected columns, using a default separator (usually a comma and a space). So, for Beth and Smith, the combined field would display "Beth, Smith". You can add this new field to your worksheets, but you have less control over the formatting than with a calculated field.

Limitations of Combined Fields

• Limited Separator Control: You cannot easily change the default separator used by the Combined Field feature. You're generally stuck with a comma.
• No Data Type Conversion: It does not automatically handle converting and combining different data types.
• Less Readability: The automatically generated name is long and might not be as clean as one you create yourself in a calculated field.

Method 3: Merging Columns at the Data Source Level

For more complex data preparation or when working with enormous datasets, it’s often more efficient to merge columns before the data even gets into your main Tableau worksheet. This can be done in two main ways: using Tableau Prep Builder or with Custom SQL.

Using Tableau Prep Builder

Tableau Prep Builder is a tool designed specifically for cleaning, shaping, and combining data before you analyze it. Using it to merge fields is incredibly intuitive.

1. Add Your Data: Connect to your data source in Tableau Prep.
2. Add a Clean Step: Create a 'Clean Step' in your flow.
3. Create Calculated Field: In the profile pane of the clean step, click 'Create Calculated Field'. Here, you can use the exact same formulas as you would in Tableau Desktop (e.g., [First Name] + " " + [Last Name]).
4. Output the Data: Once your flow is complete, you can output the result as a new data source (a .hyper file or publish it to Tableau Server) that already contains the merged 'Full Name' column.

The benefit here is that the merge calculation is done once during the data prep phase, which can improve your dashboard's performance since Tableau Desktop doesn't have to re-compute it every time.

Using Custom SQL

If you're comfortable with SQL, you can merge columns directly within your data connection using a Custom SQL query.

1. When connecting to your data, drag the "New Custom SQL" option into the window.
2. Write a query to select your columns and create the merged column. The concatenation operator varies by SQL dialect:

• For Databases like SQLite, or PostgreSQL:

SELECT [FirstName], [LastName], ([FirstName] || ' ' || [LastName]) AS FullName FROM your_table

• For Databases like SQL Server:

SELECT [FirstName], [LastName], ([FirstName] + ' ' + [LastName]) AS FullName FROM your_table

• For MySQL:

SELECT FirstName, LastName, CONCAT(FirstName, ' ', LastName) AS FullName FROM your_table

This technique offloads the processing work to the database itself, which can be faster for massive datasets. However, it's best for users who are already familiar with SQL syntax.

Which Method Should You Choose?

• Use a Calculated Field for day-to-day work. It offers the most flexibility and control directly within your Tableau workbook.
• Use Combine Fields for a quick and simple way to group dimensions for filtering or building a quick hierarchy.
• Use Tableau Prep or Custom SQL when you are doing heavy data preparation, dealing with very large data, and want to centralize your data-cleaning logic for repeated use across many workbooks.

Final Thoughts

As you've seen, Tableau gives you several paths to merge columns, from the highly flexible Calculated Field to quicker options like 'Combine Fields' and more robust solutions like Tableau Prep. Choosing the right method depends on your specific goal, the state of your data, and how much control you need over the final output.

For many teams, the learning curve and manual steps involved in tools like Tableau can become a bottleneck to getting quick answers. If you’d rather just ask a question in plain English and get an instant dashboard - without writing formulas or navigating menus - our platform might be a better fit. We built Graphed to connect to your data sources and allow you to simply describe the chart or report you need, like "Show me sales by full name," and the AI handles the data manipulation and visualization for you in real time.