Do you receive a new file from a client every month? Do you want to automatically incorporate this data into Tableau? Did you know you can Union these data sets with a wildcard or an asterisk *?
I would like to thank Heather Gough for this example and topic!
In order to do this in Tableau, your files need to have the same schema or columns in the same order.
Union Example Using Excel Workbooks
Open your workbook as usual.
Click on connect to a Microsoft Excel file for this example.
Navigate to where you have your files located.
Quick Tip - Sometimes it useful to have a separate folder for just these files, that way only one workbook will point to this location.
Select one of these files and click the Open button. Ensure the sheet from this file is in the Table field.
Drag the New Union button onto this Sheet in the Table Field. (Alternatively, you can right click on the Sheet and select Convert to Union from the drop-down.)
On the Union pop-up menu click on the Wildcard tab. You have two options where you can put the wildcard, either on the sheet or the workbook.
In my example I put the wildcard on the Workbook Matching pattern field by entering *.xlsx
This will bring in any Excel workbook located in this folder.
I then click the OK button.
Now all of the data from each workbook will appear;
I can see the data from March, April and May.
What Happens When a New File is Put Into This Folder?
If you refresh your data source, the tableau workbook should now pick up that new file in that folder as long as it has the same schema.
It picked up the June file now in this folder;
What if I Moved the March File From This Location That I Used to Create the Initial Connection?
I received the below error when I moved March to an archive folder in this folder.
This can break your dashboard so keep in mind you will have to refresh your workbook in this instance.
What if a New File Doesn't Have the Same Schema?
If one of your columns is named differently it will pull in that column as a new column. See the example below, where Name was Account Name for the April Excel spreadsheet;
Notice that there are null values in the Name column for this excel workbook.
There are also nulls for Account Name for all of the other spreadsheets.
Using a Union for Worksheets
To union in worksheets in a workbook, I simply put in an asterisk * with no text in the Sheets Matching pattern field of the Sheet pop-up for the data source.
I connected to a file that had all worksheets with the same columns in place.
