2.3.2. Grouping, Pivoting, Unpivoting, and Transposing

💡 First Principle: The same facts can be laid out wide (one column per month) or tall (one row per month). Humans read wide tables; engines aggregate tall ones. Pivoting and unpivoting rotate data between these layouts, and grouping changes how many rows represent the facts.

Group By aggregates rows, like a SQL GROUP BY. Basic mode groups by one column with one aggregation; Advanced mode groups by several columns and produces several aggregations at once. Operations include Sum, Average, Median, Min, Max, Count Rows, Count Distinct Rows, and All Rows, which keeps the grouped rows as a nested table you can expand or process later. Use Group By to reduce a fact table's grain when detail isn't needed (daily totals instead of individual transactions), which shrinks the model.

Unpivot converts columns into attribute-value rows. A budget sheet with columns Region, Jan, Feb, Mar becomes Region, Attribute (month), Value. This is the most commonly needed reshape for spreadsheet sources, because a model needs one Month column to relate to a date table, not twelve month columns. Three variants matter:

• Unpivot Columns: unpivots the selected columns.
• Unpivot Other Columns: unpivots every column except the selected ones. This is the robust choice: when next month's file adds an Apr column, it's unpivoted automatically, because the step records the columns to keep rather than the columns to rotate.
• Unpivot Only Selected Columns: hard-codes the selected column names.

Pivot Column does the reverse: values in one column become new column headers, with a values column and an aggregate function (or Don't Aggregate when each combination is unique). Use it when attribute-value data (Metric, Value) needs to become proper columns (Revenue, Cost).

Transpose swaps rows and columns entirely, typically followed by Use First Row as Headers, for sources where records run across columns instead of down rows.

⚠️ Exam Trap: When a stem says new month columns are added to the source every month and the query must keep working without edits, the answer is Unpivot Other Columns with the fixed identifier columns selected. Selecting the month columns and choosing Unpivot Columns hard-codes today's months and silently ignores new ones.

Reflection Question: A sales spreadsheet has Product in column A and one column per year from 2019 to 2026. Describe the steps that turn it into a fact table that can relate to a Date dimension, and explain why the result is better for DAX time intelligence.