Union and join basics in Tableau - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to combine sales data from two different regions and analyze total sales by product category.

📊 Data: You have two Excel files: one with sales data from the East region and one from the West region. Each file has columns: Order ID, Product Category, Sales Amount, and Order Date.

🎯 Deliverable: Create a Tableau dashboard that shows total sales by product category combining both regions.

Sample Data

Order ID	Product Category	Sales Amount	Order Date
1001	Electronics	250	2024-01-05
1002	Furniture	450	2024-01-07
1003	Clothing	150	2024-01-10
2001	Electronics	300	2024-01-06
2002	Furniture	500	2024-01-08
2003	Clothing	200	2024-01-11

1

Step 1: Connect to the East region sales Excel file in Tableau.

Use 'Connect' > 'Microsoft Excel' > select East region file.

Expected Result

East region sales data loaded with columns: Order ID, Product Category, Sales Amount, Order Date.

2

Step 2: Connect to the West region sales Excel file in Tableau and add it as a union to the East region data.

Drag West region file below East region file in the data source tab to create a union.

Expected Result

A combined table with all rows from both East and West regions.

3

Step 3: Create a new worksheet to analyze total sales by product category.

Drag 'Product Category' to Rows and 'Sales Amount' to Columns. Set aggregation to SUM.

Expected Result

Bar chart showing total sales for each product category combining both regions.

4

Step 4: Add a filter to show sales only for the month of January 2024.

Drag 'Order Date' to Filters, select 'Range of Dates', and choose 2024-01-01 to 2024-01-31.

Expected Result

Chart updates to show sales only for January 2024.

5

Step 5: Create a dashboard and add the worksheet with total sales by product category.

Use 'New Dashboard', drag the worksheet onto the dashboard canvas.

Expected Result

Dashboard displays total sales by product category for both regions combined.

Final Result

Dashboard: Total Sales by Product Category (Jan 2024)

+--------------------+------------+
| Product Category   | Total Sales|
+--------------------+------------+
| Electronics        | 550        |
| Furniture          | 950        |
| Clothing           | 350        |
+--------------------+------------+

Bar chart shows Electronics, Furniture, and Clothing sales bars sized by total sales.

✓Furniture has the highest total sales combining both regions in January 2024.

✓Electronics and Clothing have lower but significant sales.

✓Union of data from both regions allows complete sales analysis.

Bonus Challenge

Create a join between the combined sales data and a product details table to add product descriptions.

Show Hint

Use 'Product Category' as the join key between sales data and product details table.

Practice

1. Which of the following best describes a Union in Tableau?

easy

A. It filters data based on a condition.

B. It combines columns from two tables based on matching keys.

C. It creates a new calculated field.

D. It stacks rows from two or more tables with similar columns.

Solution

1. Step 1: Understand Union purpose

   A Union stacks rows from tables that have the same columns, combining data vertically.

2. Step 2: Compare with Join

   Join combines columns horizontally based on matching keys, not stacking rows.

3. Final Answer:

   It stacks rows from two or more tables with similar columns. -> Option D

4. Quick Check:

   Union = stacking rows [OK]

Hint: Union stacks rows; Join combines columns [OK]

Common Mistakes:

• Confusing Union with Join
• Thinking Union combines columns
• Assuming Union filters data

2. Which syntax correctly represents a Join in Tableau's data source page?

easy

A. Drag one table onto another and select the join type.

B. Use the UNION keyword in the data pane.

C. Create a calculated field with JOIN() function.

D. Right-click a table and select 'Append'.

Solution

1. Step 1: Identify Join method in Tableau

   In Tableau, you join tables by dragging one table onto another and choosing the join type (Inner, Left, Right, Full).

2. Step 2: Eliminate incorrect options

   Union keyword is for unions, JOIN() function does not exist, and 'Append' is not a Tableau option.

3. Final Answer:

   Drag one table onto another and select the join type. -> Option A

4. Quick Check:

   Join = drag tables + select join [OK]

Hint: Join by dragging tables together in Tableau [OK]

Common Mistakes:

• Confusing Union keyword with Join
• Looking for JOIN() function which doesn't exist
• Using 'Append' instead of Union or Join

3. Given two tables:
Table A: ID, Name
Table B: ID, Sales
What will be the result of an Inner Join on ID?

medium

A. A table with only IDs from Table A and no Sales column.

B. A table stacking all rows from both tables vertically.

C. A table with columns ID, Name, Sales containing only matching IDs.

D. A table with all IDs from both tables, including unmatched rows.

Solution

1. Step 1: Understand Inner Join behavior

   Inner Join returns rows where the join key (ID) exists in both tables, combining columns.

2. Step 2: Analyze columns and rows

   The result will have columns ID, Name, Sales and only rows with matching IDs in both tables.

3. Final Answer:

   A table with columns ID, Name, Sales containing only matching IDs. -> Option C

4. Quick Check:

   Inner Join = matching rows + combined columns [OK]

Hint: Inner Join keeps only matching keys with all columns [OK]

Common Mistakes:

• Thinking Inner Join stacks rows
• Expecting unmatched rows included
• Confusing columns included in result

4. You tried to Union two tables but got an error. What is the most likely cause?

medium

A. You forgot to select a join type.

B. The tables have different column names or data types.

C. You used a calculated field instead of a table.

D. You dragged tables instead of using the Union option.

Solution

1. Step 1: Identify Union requirements

   Union requires tables to have matching column names and compatible data types to stack rows.

2. Step 2: Understand error cause

   If columns differ in name or type, Tableau cannot stack rows, causing an error.

3. Final Answer:

   The tables have different column names or data types. -> Option B

4. Quick Check:

   Union error = mismatched columns/types [OK]

Hint: Union needs matching columns and types [OK]

Common Mistakes:

• Confusing Union errors with Join errors
• Not checking column names and types
• Trying to drag tables instead of using Union

5. You have monthly sales data split into two tables: Sales_Jan and Sales_Feb with identical columns. You also have a Customers table with customer details. How do you create a combined dataset showing all sales with customer info?

hard

A. Union Sales_Jan and Sales_Feb, then Join the result with Customers on CustomerID.

B. Join Sales_Jan with Customers, then Union with Sales_Feb.

C. Union Customers with Sales_Jan and Sales_Feb directly.

D. Join Sales_Jan and Sales_Feb on CustomerID, then Union with Customers.

Solution

1. Step 1: Combine monthly sales data vertically

   Since Sales_Jan and Sales_Feb have identical columns, use Union to stack their rows into one sales table.

2. Step 2: Enrich sales data with customer info

   Join the combined sales table with Customers on CustomerID to add customer details horizontally.

3. Final Answer:

   Union Sales_Jan and Sales_Feb, then Join the result with Customers on CustomerID. -> Option A

4. Quick Check:

   Union sales first, then Join customers [OK]

Hint: Union similar tables first, then Join related tables [OK]

Common Mistakes:

• Joining before unioning sales tables
• Trying to union unrelated tables
• Joining sales tables instead of unioning