Consider a Microsoft Excel workbook that contains multiple worksheets, with each sheet representing data for a particular geographic region. And say you want to combine the data from these sheets into one, so that you can compare figures across regions. An inclination might be to use a join query. However, what you might be looking for instead is the result of a type of union.
A join adds columns. Therefore, in this scenario, a join would not produce the result that enables you to analyze data across regions. A UNION query appends distinct rows, and UNION ALL returns all rows. In this case, as long as you have the same column headings on all worksheets, you can use a UNION ALL query.
Performing a union, instead of copying and pasting the data into one big Excel worksheet, also allows you to maintain the worksheets separately, but analyze them all as a single table in Tableau.
Use custom SQL to create the union
The steps below show a UNION ALL query using an Excel workbook that contains two worksheets for regional sales data. Each sheet represents a different region, in this case East and West.
Step 1
Open a new workbook, click Connect to Data > Microsoft Excel, and then find and select your Excel workbook.
Step 2
In the Excel Workbook Connection dialog box, select Custom SQL.
Step 3
Replace the default query with a UNION ALL query similar to the one shown below, using your worksheet names.
In this example, the worksheets do not include a region field, because each sheet represents a region. So in addition to the union, you create a column for Region in the Tableau view, plugging in the worksheet name as the value.
SELECT *, 'East' AS [Region]
FROM [East$]
UNION ALL
SELECT *, 'West' AS [Region]
FROM [West$]
Notes
- If your worksheets do include a Region column, you can simply use
SELECT * FROM [SheetName$]. - If your worksheet names have spaces in them, you need to include single quotation marks around the name in your custom SQL query.For example, for the sheet name Eastern Region, you would type ['Eastern Region$'] as part of the query.
Step 4
Click OK.
In the Tableau environment, you can see that the connection consists of a single table that contains all of the data from both worksheets.
Step 5
From the Dimensions pane, drag Region to a shelf, and then drag other fields to create a view.
The view shows how the regions compare.
Note: If you add sheets to the Excel workbook in the future, you will need to modify the Tableau data connection.
CAUTION: The UNION ALL query is processed every time Tableau queries the data source (that is, any time you drag fields around the view, use a filter, and so on). This could take a long time depending on the size of your Excel file. You can avoid these issues by taking an extract. Select Data > Extract.