"Union" Files From Two Tableau Data Connections

Tableau has made a lot of progress in regards to preparing your data and I'm not even including Tableau Prep in that statement. I particularly like the ability to union csv files, pivot your data, and creating multiple connections to be used in a single data source. Previously, if you wanted to use multiple data connections, they were separate and your only option to bring them together was data blending (unless you wanted to put the data in the same location on the back end) I try to avoid data blending at all costs. Not that data blending doesn't work, but it can be difficult for non-IT users to understand the level at which their data is being linked.

I'm using Tableau 10.5 still, so perhaps there is an easier way to do this is in the latest version of the software, but my issue was that I have 4 csv files of data in one network location and a separate csv file in a different network location that I want to union together. The issue is that Tableau Desktop does not allow you to union files across data location/connections. Below I've outlined the steps I take to hack Tableau!

Side note: the difference between a data source and a data connection is that data connection contains the information necessary to connect to a data source, of which you can have multiple in a single data source. A data source, on the other hand, is a complete collection of data connections, manipulations, filters, and/or extracts.

As you can see in the screenshot above, I have two data connections and am joining them in the window on the right. On the left side of the join I have unioned the 4 csv files I need from Location 1 and on the right side of the join I have the single csv file I need from Location 2. The hack I used to "union" these two sets of files is:

  • Select a Full Outer join
  • From the left drop down select any field
  • From the right drop down select any field that does NOT match what was selected in Step 2

Because these two data sets will not match on any of the given criteria AND a full outer join is being performed, the result is very similar to a union. The exception is that the fields from the left and right side of your data set will not align. I solved for this by creating calculated fields using the IFNULL() function, which is easy enough with a data set with a small number of columns, but it may not be the best solution if a large number of columns are needed.