Group By, Pivot & Unpivot
Reshaping is the most under-used skill in Power Query and the one that unlocks the most. Group By aggregates. Unpivot turns a human-readable cross-tab into something a machine can analyse. Pivot does the reverse. If you learn one thing from this module, make it Unpivot.
Learning Objective
Finance teams hand you spreadsheets with twelve month columns. Power BI cannot use them. Unpivot is the operation that fixes it, and understanding why is more valuable than the click itself.
- Explain the difference between wide and long data, and why Power BI needs long
- Unpivot a cross-tab, using Unpivot Other Columns for resilience
- Use Group By with multiple aggregations, including keeping all rows
- Use Pivot deliberately, and know when it is the wrong answer
- Recognise when a source shape is the real problem
Why This Matters
Here is the shape that arrives from finance every month:
| Category | Region | Jan-2025 | Feb-2025 | Mar-2025 | … |
|---|---|---|---|---|---|
| Electronics | North | 84,210 | 79,140 | 92,850 | … |
| Electronics | South | 61,330 | 58,020 | 67,410 | … |
| Apparel | North | 22,180 | 20,940 | 24,660 | … |
It is perfectly readable to a person and Power BI can do almost nothing with it. You cannot build a month trend line, because month is not a field — it is twelve separate fields. You cannot relate it to a date table. You cannot write a time intelligence measure. And when the file gains a January-2026 column, every visual referencing specific months breaks.
Concept
Wide versus long
| Wide (cross-tab) | Long (tidy) | |
|---|---|---|
| Shape | One row per entity, one column per period | One row per entity-period combination |
| Good for | Human reading, printed reports | Analysis, and every BI tool ever built |
| Adding a period | Adds a column — breaks everything downstream | Adds rows — changes nothing |
| Relating to a date table | Impossible | Straightforward |
| Time intelligence | Impossible | Works normally |
Unpivot
Transform › Unpivot Columns. Select the columns to unpivot; they collapse into two new columns: Attribute (the old column name) and Value.
The three variants, and which to use
| Command | Behaviour | Resilience |
|---|---|---|
| Unpivot Columns | Unpivots exactly the columns you selected | Fragile — a new month column is not included and is silently left as a column |
| Unpivot Other Columns | Unpivots everything except the columns you selected | Robust — select the dimensions to keep, and every current and future measure column unpivots automatically |
| Unpivot Only Selected Columns | Unpivots exactly the selection, and never more | Use when you genuinely want a fixed set |
This is the single most important habit in this lesson. It means the query describes its intent ("these are my dimensions; everything else is a measurement") rather than a hard-coded list of month names. When 2026 arrives, nothing breaks.
It is the same principle as Choose Columns over Remove Columns, and rule-based filters over ticked lists: name what stays, not what goes.
After unpivoting
- Rename the columns.
Attribute→Month,Value→Sales. Do it immediately. - Convert the attribute to something usable.
Jan-2025is text. Turn it into a real date so it can relate to a date table — usually with a Custom Column parsing it, or a mapping. - Check the row count. Rows before × columns unpivoted should equal rows after (minus any nulls, which Unpivot drops).
Usually this is what you want: no data means no observation. But if a null genuinely means zero, you have silently lost those zeros. Check the arithmetic every time.
Group By
Transform › Group By. Choose the grouping columns and one or more aggregations.
| Operation | Returns |
|---|---|
| Sum, Average, Min, Max, Median | The usual aggregates over a chosen column |
| Count Rows | How many rows in each group — no column needed |
| Count Distinct Rows | How many distinct rows |
| All Rows | A nested table of the group's rows — the powerful one |
All Rows
This returns a table value in each cell. It looks strange and it is the key to a whole class of problems: "for each customer, find their first order", "for each product, keep only the row with the highest sales".
let
Grouped = Table.Group(Source, {"CustomerKey"},
{{"AllRows", each _, type table}}),
Earliest = Table.AddColumn(Grouped, "FirstOrder", each
Table.First(
Table.Sort([AllRows], {{"OrderDate", Order.Ascending}})
)),
Expanded = Table.ExpandRecordColumn(Earliest, "FirstOrder",
{"OrderID", "OrderDate", "NetSales"})
in
Table.RemoveColumns(Expanded, {"AllRows"})Pivot
Transform › Pivot Column. The reverse of unpivot: turns distinct values in one column into new columns.
Legitimate uses of Pivot: creating a genuine attribute column from a key-value pair table (a common shape in configuration data), or preparing data for an export that must be wide. Both are real; neither is "I want the table to look like this".
Pivot's 'Don't aggregate' option
Under Advanced options. If each pivoted cell should hold exactly one value, choose Don't aggregate. If it errors, you have more than one value per cell — which means your grain assumption was wrong, and finding that out is useful.
Visual Explanation
The wide monthly sales file, unpivoted.
Group By in action
| Operation | Result |
|---|---|
| Group by Category, Region · Sum of Sales | One row per Category-Region pair with a total |
| Group by Category · Count Rows | One row per category with a row count |
| Group by Category · Sum, Average and Max in one step | Three aggregate columns at once |
| Group by Customer · All Rows | One row per customer, each holding a nested table |
Power BI Demonstration
bwb-wide-monthly-sales.csv → Transform Data. Note 30 rows and 14 columns.Category and Region. Only these two.Attribute → Month, Value → Sales.Date.FromText("01-" & [Month], [Format="dd-MMM-yyyy", Culture="en-GB"]). Set the type to Date.Jan-2026 column to a copy of the source file and refresh. It unpivots automatically — no query change needed. Now try the same with plain Unpivot Columns and watch it stay a column.Example Dataset
This file exists specifically for this lesson — 30 rows with twelve month columns spread across, exactly as a finance team would send it.
bwb-wide-monthly-sales.csv4 KB · 30 rows · 3 columns · CSV
Columns & data types
| Column | Data type | Meaning |
|---|---|---|
Category | Text | Product category. |
Region | Text | Sales region. |
Jan-2025 … Dec-2025 | Decimal | Twelve month columns. These need to become two columns: Month and Sales. |
Questions this dataset can answer
- What does this table look like after Unpivot Other Columns?
- Why can you not build a month trend line from the file as delivered?
Step-by-Step Exercise
bwb-retail-sales.csv, group by CustomerKey with All Rows, then extract each customer's earliest order date.Expected Result
| Check | Expected |
|---|---|
| Rows after unpivot | 360 (30 × 12) |
| Adding a 2026 column, Unpivot Other Columns | Unpivots automatically — 390 rows |
| Adding a 2026 column, plain Unpivot Columns | Stays as a column — silently excluded |
| Month as a Date type | 2025-01-01 through 2025-12-01 |
| Group by Category, Sum | 6 rows, one per category |
| Group by Category and Region, Sum | 30 rows — one per combination |
| After blanking one cell | 359 rows — no warning |
The fix: if blank means zero in your source, replace nulls with 0 before unpivoting. If blank means "no data", dropping the row is correct. You have to know which, and the file will not tell you — ask.
Common Mistakes
Fix: Select the dimensions to keep, then Unpivot Other Columns. Name what stays, not what goes.
Fix: Rename immediately, in the same working session, before doing anything else.
Jan-2025 as text cannot relate to a date table, sorts alphabetically, and blocks all time intelligence.Fix: Convert to a real Date — typically the first of the month — as the very next step after renaming.
Fix: Rows × unpivoted columns should equal the result. If it does not, find out what the blanks meant before deciding.
Fix: Use a matrix visual. It pivots at display time and leaves the model correct.
Professional Tip
• "I can't build a trend line from this."
• "The report breaks every January."
• "I can't compare this year to last year."
• "I have to rebuild the visuals whenever finance adds a month."
• "Why do I have twelve measures that all do the same thing?"
All five are the same problem — a wide source — wearing different clothes. Recognising the pattern quickly is one of the more visibly useful things you can do early in a project.
Two more:
- Unpivot before you clean. Fixing types and values on 12 month columns means doing the work 12 times, and it breaks when a 13th appears. Unpivot first, then clean the single value column once.
- Keep the original attribute text alongside the derived date while developing. If the date conversion produces something unexpected, having
Jan-2025next to2025-01-01makes the problem obvious immediately.
Knowledge Check
Knowledge Check
5 questions. Answer each one to see why the right answer is right.
A finance file has 30 rows and 12 month columns. After unpivoting, how many rows should you have?
Why is Unpivot Other Columns preferable to Unpivot Columns?
After unpivoting, the Month column contains text like "Jan-2025". Why must you convert it to a date?
You want a table showing categories down the side and months across the top. What should you do?
Your unpivot produced 359 rows where you expected 360. What happened, and does it matter?
Practical Challenge
Turn a hostile finance export into an analysable table
Build a query on bwb-wide-monthly-sales.csv that produces a long-format table ready to relate to a date dimension — and that will keep working when finance adds 2026. Then prove the resilience.
- Unpivoted using Unpivot Other Columns
- Columns renamed meaningfully — no
AttributeorValue - Month converted to a real Date, and typed
- Row count verified against the expected arithmetic
- A null-handling decision made explicitly and documented
- Proof that adding a 2026 column requires no query change
- A Group By producing total sales by Category and Region, with the row count checked
For the date conversion, the neatest approach prepends a day to the month label and parses with an explicit culture:
Date.FromText("01-" & [Month], [Format="dd-MMM-yyyy", Culture="en-GB"])For the null decision, ask what a blank cell in a monthly sales export most likely means for this business, and what the consequence of each interpretation would be.
Solution
Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.
The query
let
Source =
Csv.Document(File.Contents(DataFolder & "bwb-wide-monthly-sales.csv"),
[Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
#"Promote headers" =
Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
// DECISION: a blank month cell in this export means "no sales recorded",
// not "zero sales" - see the note below. Replacing with 0 would invent data.
// We therefore let Unpivot drop them, and record the expected shortfall.
#"Unpivot month columns" =
Table.UnpivotOtherColumns(#"Promote headers",
{"Category", "Region"}, // <-- name what STAYS
"Month", "Sales"),
#"Type sales as decimal" =
Table.TransformColumnTypes(#"Unpivot month columns",
{{"Sales", type number}}, "en-GB"),
#"Add month start date" =
Table.AddColumn(#"Type sales as decimal", "MonthStart",
each Date.FromText("01-" & [Month],
[Format = "dd-MMM-yyyy", Culture = "en-GB"]),
type date),
#"Reorder columns" =
Table.ReorderColumns(#"Add month start date",
{"Category", "Region", "MonthStart", "Month", "Sales"})
in
#"Reorder columns"Row count verification
| Check | Value |
|---|---|
| Source rows | 30 |
| Month columns | 12 |
| Expected rows after unpivot | 360 |
| Actual rows | 360 |
| Difference | 0 — no blank cells in this file |
The verification matters even when it passes. Recording that the file currently has no blanks means that if a future refresh produces 358, you know immediately that something changed.
The null-handling decision
Why not replace blanks with zero? Because a blank in a sales export from a finance system most plausibly means no data was submitted for that category-region-month, not sales were exactly zero. Those are different facts. Replacing with zero would:
• make averages wrong, by including a fabricated observation
• make a "zero sales" chart show a real-looking trough that never happened
• permanently destroy the distinction, since nothing downstream could tell the difference
Why not stop and ask? I would — and I have. In the meantime, dropping is the safer default because it is recoverable: if finance confirms blank means zero, I add one step. If I had replaced with zeros and they meant no-data, the damage is already in every report built on it.
The monitoring: a check query asserting
row count = (source rows × month columns). If it ever fails, blanks have appeared and I will know before anyone else does.Proving resilience
| Test | Unpivot Other Columns | Plain Unpivot Columns |
|---|---|---|
| Baseline | 360 rows | 360 rows |
Add a Jan-2026 column to the source | 390 rows — it unpivoted automatically | 360 rows — the new column is still a column |
| Was there any warning? | n/a — it just worked | None. The data is silently absent |
| Query change required | None | Must edit the step to include the new column |
The Group By
Table.Group(#"Reorder columns", {"Category", "Region"},
{
{"Total Sales", each List.Sum([Sales]), type number},
{"Average Month", each List.Average([Sales]), type number},
{"Months", each Table.RowCount(_), Int64.Type}
})Row count check: 6 categories × 5 regions = 30 rows, matching the source's 30 rows. And the Months column should read 12 for every row — if any row shows fewer, that combination had a blank month, which is the same check as before expressed a different way.
Why the Months column is worth adding
Adding a row count to a Group By costs nothing and turns the aggregation into its own validation. Any row not showing 12 is immediately visible, and it tells you exactly which category-region combination is short — which the overall row count alone would not.
Building the check into the output, rather than as a separate query you have to remember to look at, is generally the more reliable pattern.
Lesson Summary
- Wide data is for humans; long data is for analysis. Power BI needs one row per observation with the period as a field.
- Always use Unpivot Other Columns. Name the dimensions to keep, and new value columns unpivot automatically.
- Rename Attribute and Value immediately, then convert the attribute to a real Date.
- Unpivot silently drops nulls. Verify rows × columns against the result, and decide deliberately what a blank means.
- Group By supports several aggregations at once. All Rows returns a nested table and solves per-group problems, at a performance cost.
- Pivot is usually wrong in Power BI. For display, use a matrix visual instead.
- Unpivot before cleaning — fix one value column once, not twelve columns twelve times.
- Recognise the family of complaints that all mean "your source is wide".
Syllabus topics delivered
Progress
Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.