Do you know how to create Union Tables in Tableau?
Follow along to the video or check out the steps below.
Using Tableau with file data sources is very easy and practical. You just connect your file to your Tableau dashboard and boom, you can use all the data in it.
When working with sources like Excel files, you won’t always have all the data that you need in a single file.
Maybe you want to connect multiple files, with the same structure, that will complement each other.
Can Tableau read multiple files if I just place more and more into a folder?
Well, there’s a pretty cool feature in Tableau called Union Tables, where you specify how you want Tableau to look for the filename and/or sheet name and where you specify the parent and/or child folder.
Let’s go to our quick tutorial.
- Create a new workbook in Tableau, click on Connect to Data and then Microsoft Excel.
- Now navigate to the data folder (provided with this tutorial), enter any of the folders and select any of the excel files.
There is no need for selecting a specific one because we’re going to instruct Tableau to fetch all of them anyways. At this step, we just want one of the files as a model to import the others.
- This should be your result:
- Now we’ll filter the rows with NULL employee (the grand total row). On the upper right corner, click on Add, just below Filters, then Add in the new window that will pop up. Search for “Employee”, select the field and hit OK.
- In the new window, select the “Exclude” option, then select “Null” and hit OK.
- Hit OK again.
- That’s the result:
Now let’s do the “union” part.
- Right click the “Staff Utilisation” table and select Convert to Union…
- On the new window that pops up, select “Wildcard (automatic)”. And nowhere is the secret. You can specify patterns for files (workbooks) and sheets inside them. So if you have a bunch of files that have multiple sheets inside them, you can specify which ones you want to include (or exclude, depending on what you select in the dropdown).In this example, each of our files has just one sheet, so we’ll leave the “Sheet” section empty. This will tell Tableau to import all sheets. In the Workbook section, we’ll specify that we want to include all sheets that end with “STAFF.xlsx”, that’s why we put “*” at the beginning, this will tell Tableau to search for files with the suffix STAFF.xlsx, regardless of the prefix. And then, select the “Expand search to parent folder”.The “Expand search to subfolders” option will tell tableau to search for files in the subfolders relative to where the file is, so if we had child folders inside our folder structure, this option would need to be selected. The “Expand search to parent folder” option will tell Tableau to search for files in the parent folder (not folders, folder in the singular). In other words, it will navigate one level up regarding our original file and look at the folders existing there, or even to parallel folders to the one we’re originally looking for. In our case, that’s what we want, and I’ll post an image explaining why further down the tutorial.
- And that’s the result:
Note the two selected columns.
They’re generated by the Union and hold the path of the file and its sheet.
These two columns are useful for adding value to our source. We can create a “Subdivision” field by using part of the Path. Also, we can create another field to extract the date that the information is about.
Now to better illustrate an explanation of the 2 options that Tableau gives us of searching in folders:
Supposing we’re using the following file as the initial connection:
Data/Brisbane/2020-07-01 Brisbane STAFF.xlsx
When not selecting neither of the options, the highlighted items will be picked:
Because only this item is inside the folder.
When selecting just the “Expand search to subfolders” options, the highlighted items will be picked:
Because of the file, we’re connected to, Tableau is looking for the subfolders in the level our file is placed on our folder structure.
When selecting just the “Expand search to parent folder” options, the highlighted items will be picked:
It is going up one level (to the parent folder) which means the “Data” folder. And then searching for files that are parallel from the file we connected initially.
That means it will go inside each of the folders inside “Data” and get the files there, except the files inside another level of the folder, that’s why it’s not picking the folders “Subfolder” and “Subfolder 2”.
If you have both options selected, it will pick all the files in the list.
I’ve just modified the original structure of our example data files. The original structure is: