Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 56 ยท Power Query

Unpivot, Pivot and Transpose

Turn wide tables into tidy ones with Unpivot, summarize with Pivot, and flip with Transpose in Power Query, with M code and runnable pandas melt and pivot.

Spreadsheets love wide tables: one row per product, one column per month. That layout is easy for people to read and awkward for Power BI. A report wants to put month on an axis, filter by it and group by it, and that is only easy when month is a column of values, not twenty-four column headers.

The fix is Unpivot: turn the extra columns into rows. Its opposite, Pivot, spreads values back out into columns, and Transpose flips the whole table on its side. These are among the most important Power Query transformations, and they are the reason the "wide to tidy" skill is worth learning once and using forever. If you need the basics of the editor first, see The Power Query Editor.

Wide versus tidy

Wide One column per month. Good for reading.
Tidy A Month column and a Value column. Good for analysis.

Unpivot goes wide to tidy. Pivot goes back.

1 / 2

Why wide tables hurt

Imagine this table of units sold:

Product Jan Feb Mar
Pens 120 135 150
Pads 80 70 95

To chart sales by month you would need three separate fields. A new month means a new column, and your report would not know about it. In the tidy shape, each row is one observation:

Product Month Units
Pens Jan 120
Pens Feb 135
... ... ...

Now Month is a single field. A new month is a new row, and the report keeps working after the next refresh.

Unpivot columns

Select the columns you want to turn into rows (hold Ctrl to pick several), then choose Transform > Unpivot Columns. Power Query creates two columns named Attribute (the old headers) and Value (the numbers). Rename them.

M
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Product"}, "Month", "Units")

There are three variants, and the difference is important:

  • Unpivot Columns: unpivots exactly the columns you selected. If a new month column appears later, it is not unpivoted.
  • Unpivot Other Columns: select the columns to keep (such as Product), and everything else is unpivoted. This is usually the right choice, because new month columns are picked up automatically on refresh.
  • Unpivot Only Selected Columns: the same idea as the first, and it also names the chosen columns explicitly in the code.

If your data gains a new column every month, choose the columns that should stay as they are (the identifiers) and use Unpivot Other Columns. Your query will keep working without edits.

After unpivoting, set the data type of the new columns. The Month column is text such as Jan; you may want to convert it to a real date in a later step, and the Units column should be a whole number.

Pivot

Pivot Column goes the other direction. Select the column whose values should become new headers (for example Month), choose Transform > Pivot Column, pick the values column (Units), and choose how to aggregate (Sum, Count, Average, Don't Aggregate, and so on).

M
#"Pivoted Column" = Table.Pivot(
    #"Unpivoted Columns",
    List.Distinct(#"Unpivoted Columns"[Month]),
    "Month", "Units", List.Sum
)

You almost never write that by hand. The dialog generates it. What matters is the choice of aggregation: if there are several rows for the same product and month, Don't Aggregate produces an error because Power Query would not know which value to place in the cell, while Sum quietly adds them.

Use Pivot sparingly in a Power BI model. A matrix visual (see Choosing the Right Visual) can pivot at report time without changing the model, so a pivoted table is mainly useful for export or for a specific layout requirement.

Transpose

Transpose (Transform tab) swaps rows and columns: the first row becomes the first column, and so on. Use it for sources that are laid out sideways, such as a sheet with one row per attribute.

M
Transposed = Table.Transpose(Source),
#"Promoted Headers" = Table.PromoteHeaders(Transposed, [PromoteAllScalars = true])

Transposing loses the column types, so you will usually promote the first row to headers right afterward and reset the types. A closely related operator is Reverse Rows, which flips only the row order.

A common real-world case: two header rows

Sometimes a sheet has a year in one row and a month in the next, with the year only on the first column of each group. The recipe: transpose, Fill Down the year, merge the year and month into one column, transpose back, promote headers, then unpivot. This one pattern teaches the whole toolkit. Cleaning Data: Types, Nulls, Trim and Duplicates explains the Fill Down step.

Unpivot and pivot in pandas

pandas calls Unpivot melt and Pivot pivot_table. Run the following to see the table in both shapes and verify that going wide to tidy and back loses nothing.

Python melt and pivot_table

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The tidy table now has a Month field you can group by. Notice that pivot_table sorts rows and new columns alphabetically, so we restore the original order before comparing. Power Query keeps the order in which the values first appear.

Why aggregation matters in a pivot

When a key combination appears more than once, the pivot has to decide what to do. This stand-in shows the three outcomes.

Python Pivot with duplicate keys

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Pens in January has two rows, so Sum gives 120 and Average gives 60. The plain pivot refuses, just as Power Query's Don't Aggregate reports an error. The error is a feature: it tells you your data has duplicates.

As always, the M code above is what Power Query produces and cannot run on this site. The pandas code is a stand-in with the same logic.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is the tidy (unpivoted) shape better for reporting than one column per month?

    A single Month field can be placed on an axis or a slicer, and a new month adds a row instead of a column that the report does not know about.
  2. 2. Your source adds a new month column every month. Which command keeps the query working without edits?

    Unpivot Other Columns unpivots everything except the columns you pick, so month columns added later are included on refresh.
  3. 3. You pivot a column, but two rows share the same key and you chose Don't Aggregate. What happens?

    Without an aggregation function there is no rule for combining the values, so the cell shows an error.
  4. 4. What does Transpose do?

    Transpose flips the table on its side. Types are lost, so you typically promote the first row to headers and reapply data types.

Summary

  • Wide tables are good for people; tidy tables (one observation per row) are good for analysis.
  • Unpivot Other Columns is the safe default because new columns are included automatically.
  • Pivot spreads values into columns and needs an aggregation; Don't Aggregate fails on duplicate keys.
  • Transpose swaps rows and columns; promote headers and reset types afterward.
  • In pandas the equivalents are melt, pivot_table and .T.

Next, combine tables in Merge and Append Queries.