Home Course home Curriculum Learner dashboard Skill tree Business projects Datasets Final project
🧹 Module 3 · Power Query & Data Transformation Intermediate LESSON 21 / 72

Group By, Pivot & Unpivot

31 min 5 quiz questions 1 practical challenge 5 syllabus topics

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.

Lesson 21 of 72 · Power Query & Data Transformation 29%
01

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.

By the end of this lesson you will be able to
  • 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
02

Why This Matters

Here is the shape that arrives from finance every month:

Wide — human-readable, machine-hostile
CategoryRegionJan-2025Feb-2025Mar-2025…
ElectronicsNorth84,21079,14092,850…
ElectronicsSouth61,33058,02067,410…
ApparelNorth22,18020,94024,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.

The shape Power BI wants
One row per observation, with the dimensions as columns and one value column. The wide table above becomes three columns — Category, Region, Month, Sales — and 36 rows instead of 3 rows of 12. Then month is a field, it relates to a date table, and adding 2026 changes nothing about the model.
03

Concept

Wide versus long

Wide (cross-tab)Long (tidy)
ShapeOne row per entity, one column per periodOne row per entity-period combination
Good forHuman reading, printed reportsAnalysis, and every BI tool ever built
Adding a periodAdds a column — breaks everything downstreamAdds rows — changes nothing
Relating to a date tableImpossibleStraightforward
Time intelligenceImpossibleWorks 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

CommandBehaviourResilience
Unpivot ColumnsUnpivots exactly the columns you selectedFragile — a new month column is not included and is silently left as a column
Unpivot Other ColumnsUnpivots everything except the columns you selectedRobust — select the dimensions to keep, and every current and future measure column unpivots automatically
Unpivot Only Selected ColumnsUnpivots exactly the selection, and never moreUse when you genuinely want a fixed set
Always use Unpivot Other Columns
Select the columns you want to keep — Category and Region — then right-click and choose Unpivot Other Columns.

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

  1. Rename the columns. Attribute → Month, Value → Sales. Do it immediately.
  2. Convert the attribute to something usable. Jan-2025 is 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.
  3. Check the row count. Rows before × columns unpivoted should equal rows after (minus any nulls, which Unpivot drops).
Unpivot drops nulls
If a cell is empty, unpivoting does not produce a row for it. A wide table with 3 rows × 12 month columns should produce 36 rows — if you get 34, two cells were empty.

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.

Aggregation options
OperationReturns
Sum, Average, Min, Max, MedianThe usual aggregates over a chosen column
Count RowsHow many rows in each group — no column needed
Count Distinct RowsHow many distinct rows
All RowsA 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".

Power Query M · Keep the earliest order per customer
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"})
Group By with All Rows is powerful and can be slow on large tables, because it materialises a nested table per group. On a database source it also breaks folding. Use it where it earns its place; for simple aggregation, the ordinary operations fold and are far faster.

Pivot

Transform › Pivot Column. The reverse of unpivot: turns distinct values in one column into new columns.

Pivot is usually the wrong answer in Power BI
It produces the wide shape that Power BI cannot analyse. If your instinct is to pivot so a table visual looks right, use a matrix visual instead — it pivots at display time without damaging your model.

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.

04

Visual Explanation

The wide monthly sales file, unpivoted.

Group By in action

Before and after Group By on Category and Region
OperationResult
Group by Category, Region · Sum of SalesOne row per Category-Region pair with a total
Group by Category · Count RowsOne row per category with a row count
Group by Category · Sum, Average and Max in one stepThree aggregate columns at once
Group by Customer · All RowsOne row per customer, each holding a nested table
05

Power BI Demonstration

Load the wide file
Get data › Text/CSV → bwb-wide-monthly-sales.csv → Transform Data. Note 30 rows and 14 columns.
Select the dimension columns
Ctrl-click Category and Region. Only these two.
Unpivot Other Columns
Right-click the selection → Unpivot Other Columns. Check the row count: 360.
Verify the arithmetic
30 × 12 = 360. If your count is lower, empty cells were dropped.
Rename the new columns
Attribute → Month, Value → Sales.
Convert Month to a date
Add Column › Custom Column: Date.FromText("01-" & [Month], [Format="dd-MMM-yyyy", Culture="en-GB"]). Set the type to Date.
Prove the resilience
Add a 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.
Group By with one aggregation
Transform › Group By. Group by Category, aggregate Sum of Sales.
Group By with several aggregations
Undo, then Group By again — this time Advanced, grouping by Category and Region, with Sum of Sales, Average of Sales and Count Rows in one step.
Try All Rows
Group by Category with the operation All Rows. Click into a cell in the resulting column and note that it contains a whole table.
Pivot it back, and see the problem
On the long table, Pivot Column on Month with Sum of Sales. You get the wide shape back — and you have just destroyed everything the unpivot achieved. Undo.
Use a matrix visual instead
Load the long table, then in Report view build a matrix with Category on rows and Month on columns. Same appearance, model intact.
TransformUnpivot ColumnsUnpivot Other Columns
06

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.csv
4 KB · 30 rows · 3 columns · CSV
Business context
Sales by category and region with twelve month columns spread across the row — the shape a finance team hands you from Excel. Power BI cannot use this directly. Unpivoting it into long format is the whole point of the exercise.

Columns & data types

ColumnData typeMeaning
CategoryTextProduct category.
RegionTextSales region.
Jan-2025 … Dec-2025DecimalTwelve 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?
07

Step-by-Step Exercise

Unpivot the wide file properly
Using Unpivot Other Columns. Verify 360 rows.
Convert Month to a real date
And confirm the type is Date, not text.
Test resilience both ways
Add a 2026 column to a copy of the source. Refresh a query built with Unpivot Other Columns and one built with plain Unpivot Columns. Record what each does.
Aggregate with Group By
Produce total sales by Category, by Region, and by Category and Region together.
Use multiple aggregations in one step
Sum, Average, Min, Max and Count Rows, grouped by Category.
Try All Rows on the retail sales file
Load bwb-retail-sales.csv, group by CustomerKey with All Rows, then extract each customer's earliest order date.
Deliberately create a null in the wide file
Blank one month cell, refresh, and confirm the row count drops to 359. Note that nothing warned you.
Decide what that null should mean
Write one sentence: was that month zero sales, or no data? What would you do about it?
08

Expected Result

Expected results
CheckExpected
Rows after unpivot360 (30 × 12)
Adding a 2026 column, Unpivot Other ColumnsUnpivots automatically — 390 rows
Adding a 2026 column, plain Unpivot ColumnsStays as a column — silently excluded
Month as a Date type2025-01-01 through 2025-12-01
Group by Category, Sum6 rows, one per category
Group by Category and Region, Sum30 rows — one per combination
After blanking one cell359 rows — no warning
The blank cell result is the one to remember
The row count drops from 360 to 359 and nothing tells you. If that blank meant "no sales that month", you have lost a legitimate zero, and any average will be computed over 359 observations instead of 360 — slightly and invisibly wrong.

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.
09

Common Mistakes

Using Unpivot Columns instead of Unpivot Other Columns
Hard-codes the list of columns to unpivot. Next year's columns are silently left as columns and their data never enters the analysis.

Fix: Select the dimensions to keep, then Unpivot Other Columns. Name what stays, not what goes.
Leaving the unpivoted columns named Attribute and Value
Meaningless names propagate into every measure, visual and relationship built on them.

Fix: Rename immediately, in the same working session, before doing anything else.
Leaving the month as text
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.
Not checking the row count after unpivoting
Unpivot silently drops nulls, so legitimate zeros can disappear without any indication.

Fix: Rows × unpivoted columns should equal the result. If it does not, find out what the blanks meant before deciding.
Pivoting to make a table visual look right
Destroys the long shape the model needs, in order to fix a display problem.

Fix: Use a matrix visual. It pivots at display time and leaves the model correct.
10

Professional Tip

Unpivot is the fix for a whole family of complaints
When someone says any of the following, the answer is almost always unpivot:

• "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-2025 next to 2025-01-01 makes the problem obvious immediately.
11

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?

12

Practical Challenge

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.

Requirements
  • Unpivoted using Unpivot Other Columns
  • Columns renamed meaningfully — no Attribute or Value
  • 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:

Power Query M
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.

13

Solution

Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.

The query

Power Query M · Wide to long, resiliently
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

CheckValue
Source rows30
Month columns12
Expected rows after unpivot360
Actual rows360
Difference0 — 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

The decision, and the reasoning
Decision: let unpivot drop blank cells, and monitor the row count.

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

TestUnpivot Other ColumnsPlain Unpivot Columns
Baseline360 rows360 rows
Add a Jan-2026 column to the source390 rows — it unpivoted automatically360 rows — the new column is still a column
Was there any warning?n/a — it just workedNone. The data is silently absent
Query change requiredNoneMust edit the step to include the new column
The right-hand column is the point of the whole lesson
With plain Unpivot Columns, January 2026's sales sit in the model as a column nobody queries. No error, no warning, no visual breaks — the numbers are simply incomplete, and will stay incomplete until someone happens to notice that the trend line stops in December.

The Group By

Power Query M · Totals by Category and Region
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.

14

Lesson Summary

Key takeaways
  • 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

Group ByPivotUnpivotWide vs long dataUnpivot Other Columns
15

Progress

Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.

Lesson complete — 29% of Power BI Mastery done
Progress saved. Keep the momentum: the next lesson builds directly on this one.