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.
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.
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.
Tableau will automatically append the rows onto the original, identifying matching fields by their shared name.
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.
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.
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.
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.
And there you have it, you now have all your data nicely brought together, ready to be visualised in Tableau!