Power BI Pivot Unpivot and Group By

Data often arrives in a shape that suits human eyes but frustrates charts. Power Query includes three tools that reshape tables: Unpivot turns wide tables into long ones, Pivot does the reverse, and Group By summarizes rows into totals.

Wide Tables and Long Tables

A wide table spreads one kind of value across many columns. A monthly sales sheet with columns named Jan, Feb, and Mar is a classic wide table. A long table keeps one kind of value in a single column and adds a label column that says which month each row belongs to. Power BI visuals prefer long tables.

WIDE TABLE                       LONG TABLE
+-------+-----+-----+-----+      +-------+-------+-------+
| Shop  | Jan | Feb | Mar |      | Shop  | Month | Sales |
+-------+-----+-----+-----+      +-------+-------+-------+
| North | 100 | 120 | 90  |      | North | Jan   | 100   |
| South | 80  | 95  | 110 |      | North | Feb   | 120   |
+-------+-----+-----+-----+      | North | Mar   | 90    |
                                 | South | Jan   | 80    |
   <== PIVOT   UNPIVOT ==>       | South | Feb   | 95    |
                                 | South | Mar   | 110   |
                                 +-------+-------+-------+

Unpivot Columns

Unpivot converts column headers into row values. After the change, a chart can place Month on the axis and Sales on the height of the bars with no extra effort.

Steps to Unpivot

1. Open the table in Power Query Editor.
2. Select the columns that hold the values, such as Jan, Feb, and Mar.
3. Right-click a selected header and choose Unpivot Columns.
4. Rename the new Attribute column to Month and the Value column to Sales.

The option Unpivot Other Columns handles files where new month columns appear over time. Select the columns to keep, such as Shop, and unpivot everything else. Future columns flow in automatically.

Pivot Column

Pivot spreads values from one column into new column headers. Report designers use it to build a compact summary table, such as one row per shop and one column per month.

Steps to Pivot

1. Select the column whose values become headers, such as Month.
2. Choose Pivot Column on the Transform tab.
3. Pick the Values Column, such as Sales.
4. Open Advanced options and select an aggregation, such as Sum.

Group By

Group By collapses many rows into fewer rows with totals, averages, or counts. Think of a cashier counting a day's receipts and writing one total per shop on a summary slip.

Before Group By               After Group By (Sum of Sales)
 North  Jan  100               North   310
 North  Feb  120      ==>       South   285
 North  Mar   90
 South  Jan   80
 South  Feb   95
 South  Mar  110

Steps to Group

1. Select Group By on the Transform tab.
2. Choose the column to group on, such as Shop.
3. Name the new column, pick an operation such as Sum, and select the column to calculate.
4. Click Advanced to group on more than one column.

Available operations include Sum, Average, Median, Min, Max, Count Rows, and Count Distinct Rows.

When to Use Group By Versus a Measure

Group By permanently reduces the rows in the model. Detail disappears from the dataset after loading. Measures calculate totals at report time and keep every row available for deeper analysis. Choose Group By for huge tables where the detail has no value. Choose measures when users may want to drill into individual records.

Worked Example: Cleaning a Survey Sheet

A school collects survey scores in a sheet with columns Student, Math, Science, and English. The wide shape blocks a chart that compares subjects. Unpivot Other Columns on Student produces Student, Subject, and Score. A clustered column chart now shows the average score per subject in seconds.

Key Points

• Unpivot turns column headers into rows and suits chart building.
• Pivot spreads row values into columns for compact summaries.
• Group By reduces rows into totals, averages, or counts.
• Unpivot Other Columns handles files that gain new columns over time.
• Use measures instead of Group By when detail rows still matter.