can I even do a union between worksheets from separate excel connections?
Dave, Would it be possible for you to use Tableau 10.1? If so, you could have several excel files be joined using wildcard union: https://onlinehelp.tableau.com/current/pro/desktop/en-us/help.htm#union.html#union_wildcard. The resulting table will include the name of the sheet & the file where data came from.
As Santiago suggests, I believe joining files comes as a new capability in version 10.
Reference to older thread may be helpful:
The discussion in this thread was for a workbook where I add new school data (not actually fruit sales) every six weeks period from excel reports. I move the sheets to the same file and append the query. I needed all student groups to appear (male, female, race1, race2, atrisk, or whatever) regardless of whether each new worksheet included that group. For example, one year the worksheet may contain financial assistance, another year that field may be gone. And then I needed it across all school locations.
SELECT [2014_1$].[STUDENT_GROUP] as [All Student Groups],
[2014_1$].[LOC_ID] as [All Locations]
SELECT [2014_2$].[STUDENT_GROUP] as [All Student Groups],
[2014_2$].[LOC_ID] as [All Locations]