Module 2: Power Query: get and clean data · Lesson 2.4 · about 14 min
Unpivot, pivot and group by
Spreadsheets are wide; Power BI wants long. Unpivot month columns into rows, pivot back when needed, and group rows to summarise them, watching each step change the table.
Module 2 · about 14 minutes · read first: 2.2. You’ll be able to reshape wide tables into long ones, and summarise rows with Group by.
1. Why this matters
Planning and finance teams send wide spreadsheets: a column per month, a column per store. They are easy for people to read and hard for Power BI to use. Turning them into long tables is a daily job.
2. Unpivot in one picture
The data as it arrives. Click the steps in order to clean it.
MSource = Csv.Document(File.Contents("till-export.csv"))
Unpivot other columns (select Store, then Transform > Unpivot columns > Unpivot other columns) turns each month column into rows: 3 stores × 3 months = 9 rows with Store, Attribute, Value. Rename Attribute to Month and Value to Target. Now one Target column can be summed, filtered by month and compared with sales.
Use “Unpivot other columns” rather than selecting the months: if April appears next month, it is unpivoted automatically.
Pivot is the reverse (values of one column become column headers). You’ll rarely need it for a model, but it is useful to build a small summary for export.
3. Group by
The data as it arrives. Click the steps in order to clean it.
MSource = Csv.Document(File.Contents("till-export.csv"))
Transform > Group by collapses rows that share values in the group columns and computes aggregations (Sum, Count rows, Average, Min, Max) for each group. It’s the Power Query version of a pivot table or SQL GROUP BY.
When to use it: building a separate summary table, or when the source has far more detail than any report needs (for example, sensor readings every second, when reports only ever look at hours). When not to: on your main fact table, because measures can only slice by what’s left. In DAX the same summary is SUMMARIZECOLUMNS(Store[Store], "Sales", [Total Sales]) (Lesson 5.7), worked out on demand without losing detail.
Principle
Principle: tidy data has one column per variable and one row per observation. “Month” is one variable, so it belongs in one column, not twelve.
Check: if you would need to change a measure every time a new month, store or product appears, the table is too wide.
4. Worked example
Northbay’s planning team sends targets as a wide sheet: Store plus 12 month columns. In Power Query: Use first row as headers, select Store, Unpivot other columns, rename to Month and Target, change Month into a proper YearMonth text like 2025-01, set Target to a decimal number. 5 stores × 12 months = 60 rows. That’s exactly the shape of the Targets table in the course data.
5. Practice
- A sheet has Product plus columns Q1, Q2, Q3, Q4. How many rows after unpivoting 12 products?
- You grouped Sales by Store and Month in Power Query. Now the manager wants sales by category. What happened?
- Which step turns a list of stores and months back into a table with a column per month?
Answers
- 12 × 4 = 48 rows.
- Category was grouped away. Remove the Group by and let a DAX measure summarise instead.
- Pivot column (Month as the column, Target as the values).
Check yourself
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Goods is a made-up shop and its numbers are generated. Power BI changes every month: menu names can move, so check Microsoft Learn if a button is not where the lesson says. The practice editor runs in your browser; nothing you type is sent anywhere.