Transformations and Joins
What to know
Core transformation tools include Join (inner, left, right, full), Union (stack rows by position or by name), Summarize (group-by with aggregations), Transpose/Crosstab (wide-to-long and long-to-wide), and Unique/Distinct. Understand join output: matched rows plus left/right unmatched outputs, and how duplicate keys multiply rows (a many-to-many join inflates counts).
Traps
- Many-to-many joins silently duplicating rows and inflating downstream sums.
- Union by position misaligning columns when schemas differ; prefer union by name.
- Summarize dropping nulls in group-by or producing unexpected counts.
- Transpose vs. Crosstab confusion: transpose goes wide to long; crosstab goes long to wide.
- Join vs. Find/Replace vs. Append: each has different output shapes.
Rule of thumb
Before joining, check key uniqueness on each side. If a key is not unique on the "one" side, decide whether you need aggregation first.
Worked example: Joining orders to a customer table yields more rows than orders. The customer table has duplicate customer IDs. Fix: Summarize the customer table to one row per ID (or use Unique) before the join, then verify the row count equals the original order count.