All lessons

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

Try it: Unpivoting a targets sheet

The data as it arrives. Click the steps in order to clean it.

MSource = Csv.Document(File.Contents("till-export.csv"))

Each step works on the result of the one before, and Power Query replays them all on every refresh, so next month's file gets the same cleaning. Highlighted: new or changed. Faded with a line through: removed by this step.

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

Try it: Grouping order lines by store

The data as it arrives. Click the steps in order to clean it.

MSource = Csv.Document(File.Contents("till-export.csv"))

Each step works on the result of the one before, and Power Query replays them all on every refresh, so next month's file gets the same cleaning. Highlighted: new or changed. Faded with a line through: removed by this step.

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

  1. A sheet has Product plus columns Q1, Q2, Q3, Q4. How many rows after unpivoting 12 products?
  2. You grouped Sales by Store and Month in Power Query. Now the manager wants sales by category. What happened?
  3. Which step turns a list of stores and months back into a table with a column per month?
Answers
  1. 12 × 4 = 48 rows.
  2. Category was grouped away. Remove the Group by and let a DAX measure summarise instead.
  3. Pivot column (Month as the column, Target as the values).

Check yourself

1A targets sheet has columns Store, Jan, Feb, …, Dec. What shape should it be for Power BI?
2When you unpivot, which columns do you select before "Unpivot other columns"?
3Why might you avoid Group by in Power Query for the main sales table?

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.