Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI Power Query Unpivot


Share

Wide and Long Tables

The Poke Mart managers set a sales target for every store and every month of 2025. They keep the targets in an Excel file:

Download poke_mart_targets_2025.xlsx

The sheet Targets has one row per store and one column per month:

ABCDEFGHIJKLMN
1RegionStoreJanFebMarAprMayJunJulAugSepOctNovDec
2KantoViridian City5100065000750008700045000880006200070000540005500043000158000
3Cerulean City52000500007200041000710006800078000135000690008900010100076000
4JohtoGoldenrod City10400055000104000950001090001070001450001790007700081000110000141000
5Violet City250003400041000330006100046000950004800041000450004400072000
6HoennSlateport City5900054000770008600075000770007900091000530005600092000144000
7Lilycove City5300055000310004500088000710009300011000091000650003900095000

This layout is called a wide table.

It is easy for people to read, but hard to analyze:

  • There is no Month column, so you cannot put the month on a chart axis or in a slicer.
  • To get the total target, you have to add up 12 separate columns.
  • To compare targets with actual sales month by month, both tables need a month field.
  • When months are added, for example for 2026, each one is a new column, and every visual that uses the months must be changed.

Power BI works best with a long table: one row for each store and month, with the month in its own column:

ABCRegionABCStoreABCMonth123Target
KantoViridian CityJan51000
KantoViridian CityFeb65000
KantoViridian CityMar75000
............

6 stores times 12 months gives 72 rows.

Turning columns into rows like this is called unpivoting.


Load the Targets File

This page continues the cleanup from Replace Values and Fill Down.

Open pq_cleanup.pbix, the report you saved there, and select Home > Transform data.

Add the targets file as a new query, next to the 2024 sales:

  1. On the Home tab, select New Source > Excel Workbook.
  2. Find poke_mart_targets_2025.xlsx and select Open.
  3. In the Navigator, select Targets, and then select OK.

A new query called Targets appears in the Queries pane.

Power Query uses the first row as column headers and sets the data types, just as it did for the CSV file:

ABCRegionABCStore123Jan123Feb123Mar123Apr123May123Jun123Jul123Aug123Sep123Oct123Nov123Dec
KantoViridian City5100065000750008700045000880006200070000540005500043000158000
nullCerulean City52000500007200041000710006800078000135000690008900010100076000
JohtoGoldenrod City10400055000104000950001090001070001450001790007700081000110000141000
nullViolet City250003400041000330006100046000950004800041000450004400072000
HoennSlateport City5900054000770008600075000770007900091000530005600092000144000
nullLilycove City5300055000310004500088000710009300011000091000650003900095000

The query has 6 rows and 14 columns.

The empty Region cells are null.



Step 1: Fill Down the Region

First fill the empty Region cells, as you learned in Replace Values and Fill Down:

  1. Select the Region column.
  2. On the Transform tab, select Fill > Down.
ABCRegionABCStore123Jan...
KantoViridian City51000...
KantoCerulean City52000...
JohtoGoldenrod City104000...
JohtoViolet City25000...
HoennSlateport City59000...
HoennLilycove City53000...

Do this before you unpivot.

The unpivot copies each store's Region onto 12 rows, one for each month.

Without Fill Down, 36 of the 72 rows would have no region.


Step 2: Unpivot Other Columns

  1. Select the Region column header.
  2. Hold down Ctrl and select the Store column header. Both columns are now selected.
  3. On the Transform tab, select the arrow next to Unpivot Columns, and then select Unpivot Other Columns. The command is also on the right-click menu of the selected headers.

The Targets query in the Power Query Editor with the Region and Store columns selected and the Unpivot Columns menu on the Transform tab open, showing Unpivot Columns, Unpivot Other Columns and Unpivot Only Selected Columns

Power Query keeps Region and Store as they are, and turns the 12 month columns into two new columns:

  • Attribute holds the old column names: Jan, Feb, and so on to Dec.
  • Value holds the numbers that were under them.
ABCRegionABCStoreABCAttribute123Value
KantoViridian CityJan51000
KantoViridian CityFeb65000
KantoViridian CityMar75000
KantoViridian CityApr87000
KantoViridian CityMay45000
KantoViridian CityJun88000
............
HoennLilycove CityDec95000

The query now has 72 rows and 4 columns.


Step 3: Rename the New Columns

  1. Double-click the Attribute header, type Month, and press Enter.
  2. Double-click the Value header, type Target, and press Enter.
ABCRegionABCStoreABCMonth123Target
KantoViridian CityJan51000
KantoViridian CityFeb65000
KantoViridian CityMar75000
KantoViridian CityApr87000
KantoViridian CityMay45000
KantoViridian CityJun88000
............
HoennLilycove CityDec95000

Target should be a whole number column, with the 123 icon in its header.

If your Target column shows ABC123 instead, select the icon and choose Whole Number.


Check the Totals

Unpivoting only moves numbers around.

It must not change the total.

The 72 targets must add up to 5,426,000, the same as the 72 number cells in the original sheet.

You can check the total in Power Query:

  1. Select the Target column.
  2. On the Transform tab, select Statistics > Sum. The preview now shows one number: 5426000. The new step is called Calculated Sum.
  3. Delete the Calculated Sum step with the X next to it in Applied Steps, so the query returns the 72 rows again.

Once the data is loaded into the report, a table visual of Target by Region shows these values:

RegionTarget
Hoenn1,779,000
Johto1,892,000
Kanto1,755,000
Total5,426,000

Power BI shows these numbers without thousands separators, for example 1779000.


Unpivot and Pivot Commands

The Transform tab has three unpivot commands, and one command for the opposite direction:

CommandWhat it does
Unpivot ColumnsYou select the columns to turn into rows (here: Jan to Dec).
Unpivot Other ColumnsYou select the columns to keep (here: Region and Store). All the other columns are turned into rows.
Unpivot Only Selected ColumnsLike Unpivot Columns, but only exactly the columns you selected. Columns added to the source later stay as they are.
Pivot ColumnThe opposite of unpivot: the values of one column become new column headers. Pivoting Month with the values from Target gives back the wide table.

Unpivot Other Columns is the best choice here.

You name only the two columns that stay, and if next year's file has more month columns, they are unpivoted too.

Pivot Column does what an Excel PivotTable does with a field in the Columns area: it spreads the values of one field across columns.

Unpivot goes back the other way.

The Targets query is ready.

Select Close & Apply, then File > Save to save pq_cleanup.pbix.

In the next chapter you go back to the sales data and add the 2025 orders.



Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.