Marrying Data - How to Union Data in Tableau

by Nick Jastrzebski

Sometimes data is just meant to be together.

When you’ve got identically formatted data spread across multiple sheets, the simplest solution for bringing that into Tableau is a union. This process just adds rows to the table from each sheet.

There are two main ways to do this, one manual and one automatic. I’ll take you through both methods, step by step.

Specific unions

Here I’ll use an example of bringing together multiple sheets from an excel file. Below you can see on the left that I have three sheets in my file: Air, Land and Sea.

The data source window in Tableau – I have connected to my source but not brought in any sheets yet.

I can union these three together simply by dragging them into the Data Pane one at a time. After adding the first one, the subsequent ones can be dragged beneath the first until the little orange box which reads “Drag table to union” appears.

Drag a new sheet on top of the existing one until this appears…

…then the data will be added as a union.

Tableau will automatically append the rows onto the original, identifying matching fields by their shared name.

Before union…

…after union.

Caveat: Using the manual union restricts your data input to only within a single data connection. Attempting to bring in data from another connection will force it to become a join instead. You can use a wildcard union, detailed below, to side-step this limitation.

Wildcards unions

The second option for unions is by using a wildcard. Once the first sheet has been added to the Data Pane, click the drop down and convert it into a union. A new window will then appear, offering the choice of setting up either a specific union (as above) or a wildcard union.

The union setting window looks like this. Here I’ve selected the wildcard option.

This setting tells Tableau to look at specific sheets within a workbook, or at workbooks within a given directory and union together all the data that it finds. It can also be used to exclude specific sheets or workbook from the union as required.

All three sheets from my workbook have been automatically added to my data union.

But wait, my fields have different names!

In the example above, my air sheet has fields with the same data as land and sea, but with differing names. These field names are mismatched.

If fields have mismatched names, Tableau will create a new field, simply adding nulls to entries where no data exists for that field name.

This can be rectified by highlighting the mismatch fields and tell Tableau to merge them together. It will automatically name the new merged field as a combination of the two. The field can then be renamed to whatever is desired.

Selecting this option will pull together both fields. Beware! If the opposite field isn’t null, Tableau will pick one of the values at random to include.

And there you have it, you now have all your data nicely brought together, ready to be visualised in Tableau!

The data from each sheet has been successfully added to the union, ready for visualisation.


Nick Jastrzebski

Fri 30 Nov 2018

Fri 02 Nov 2018