Power Query: Interactive Tutorial

Core Concepts Visualized

Before you touch the simulators, click "Play Demo" on each card below to see exactly how data moves and transforms.

Unpivot Columns

Wide Data (Input)

Product	Jan	Feb
Apple	10	15
Pear	20	25

Tall Data (Output)

Product	Attribute	Value
Click play to watch data move...

Pivot Column

Tall Data (Input)

Store	Metric	Value
A	Sales	$50
A	Costs	$20

Wide Data (Output)

Store	...	...
Click play to reshape...

Group By (Summarize)

Detailed Data (Input)

Region	Amount
North	100
North	50
North	200

Region	Total (Sum)
Click play to aggregate...

Case 1: The Expense Report

The Analytical Goal

You need a reusable summary of Total Q1 Expenses by Store Location. The reshaped data can also be loaded to Excel for PivotTable analysis.

The Data Problem

"Month" is not a column you can filter by; the months are spread horizontally as headers. You must Unpivot the data, then Group By store.

Store Location	Account Name	Jan 2026	Feb 2026	Mar 2026
Spokane	Rent	$5,000	$5,000	$5,000
Spokane	Payroll	$12,400	$12,100	$12,800
Seattle	Rent	$8,500	$8,500	$8,500
Seattle	Payroll	$18,200	$18,400	$19,100

Store Location	Account Name	Attribute (Month)	Value (Amount)
Spokane	Rent	Jan 2026	$5,000
Spokane	Rent	Feb 2026	$5,000
Spokane	Rent	Mar 2026	$5,000
Spokane	Payroll	Jan 2026	$12,400
...

Store Location	Total Q1 Expenses
Spokane	$52,300
Seattle	$81,200

Query Settings

APPLIED STEPS

Case 2: Logistics Report

The Analytical Goal

Calculate exact fleet costs grouped by Location AND Vehicle Type.

The Data Problem

The Location and Vehicle are mashed together into one string. You must Split the column first, Unpivot, and do an Advanced Group By.

Loc_Vehicle	Jan 2026	Feb 2026	Mar 2026
Spokane_Forklift	$1,200	$1,400	$1,300
Spokane_DeliveryVan	$2,500	$2,200	$2,600
Seattle_Forklift	$1,800	$1,900	$1,700

Location	Vehicle	Jan 2026	Feb 2026	Mar 2026
Spokane	Forklift	$1,200	$1,400	$1,300
Spokane	DeliveryVan	$2,500	$2,200	$2,600
Seattle	Forklift	$1,800	$1,900	$1,700

Location	Vehicle	Attribute	Value
Spokane	Forklift	Jan 2026	$1,200
Spokane	Forklift	Feb 2026	$1,400
...

Location	Vehicle	Total Cost
Spokane	Forklift	$3,900
Spokane	DeliveryVan	$7,300
Seattle	Forklift	$5,400

Query Settings

APPLIED STEPS

Case 3: Spokane Hardware POS

The Analytical Goal

Find out exactly which products are selling the most by counting the total number of times each unique item was purchased today.

The Data Problem

The Point of Sale system lumps all items from a single receipt into one comma-separated cell. You must Split the text, Unpivot the resulting columns, and group them to Count Rows.

Receipt ID	Items
1001	Hammer, Nails
1002	Wrench
1003	Paint, Brushes, Tape
1004	Nails, Tape

Receipt ID	Items.1	Items.2	Items.3
1001	Hammer	Nails	null
1002	Wrench	null	null
1003	Paint	Brushes	Tape
1004	Nails	Tape	null

Receipt ID	Attribute	Value (Product)
1001	Items.1	Hammer
1001	Items.2	Nails
1002	Items.1	Wrench
1003	Items.1	Paint
... remaining items below

Value (Product)	Purchase Count
Hammer	1
Nails	2
Wrench	1
Paint	1
Brushes	1
Tape	2

Query Settings

APPLIED STEPS

Case 4: River City Roasters

The Analytical Goal

You want one row per store with separate columns for each inventory item. Because each store-item combination appears more than once, you also need to aggregate those repeated values into a single result.

The Data Problem

The inventory system exports tall attribute-value data with repeated Store × Metric combinations. You need to Pivot the Metric column and choose an aggregation function to decide how multiple values should be combined in each output cell.

Store	Metric	Value
Downtown	Coffee Beans (lbs)	30
Downtown	Coffee Beans (lbs)	20
Downtown	Milk (gal)	12
Downtown	Milk (gal)	8
Valley	Coffee Beans (lbs)	25
Valley	Coffee Beans (lbs)	20
Valley	Milk (gal)	10
Valley	Milk (gal)	8

Store	Coffee Beans (lbs)	Milk (gal)
Downtown	50	20
Valley	45	18

Query Settings

APPLIED STEPS

Aggregation Explorer

Pivot creates one output cell for each Store × Metric combination. When more than one source row lands in the same cell, Power Query needs an aggregation rule. Change the function below and watch the output.

Aggregate Value Function

Repeated source values

Store	Metric	Value
Downtown	Coffee Beans	30
Downtown	Coffee Beans	20
Downtown	Milk	12
Downtown	Milk	8
Valley	Coffee Beans	25
Valley	Coffee Beans	20
Valley	Milk	10
Valley	Milk	8

Pivot result

Store	Coffee Beans	Milk

Key idea: Don't Aggregate is appropriate only when each output cell receives at most one source value. Here, every Store × Metric combination has two source rows, so Power Query needs a function such as Sum, Average, Minimum, Maximum, or Count.

Prediction Challenge

Predict the table before Power Query builds it

You have now completed all four practice cases. Study each source dataset and its Power Query settings, then choose the exact output table you think Power Query will create. Submit your prediction to reveal the transformation.

1 Unpivot Prediction

Wide → Tall

Source data

Product	Jan	Feb
Apple	10	15
Pear	20	25

Power Query setting

Selected columns: Jan, Feb

Command: Unpivot Columns

What will the resulting table look like?

Option A

Product	Jan	Feb
Apple	10	15
Pear	20	25

Option B

Product	Attribute	Value
Apple	Jan	10
Apple	Feb	15
Pear	Jan	20
Pear	Feb	25

Option C

Product	Total
Apple	25
Pear	45

Keep ProductJan/Feb become AttributeNumbers become Value

Before

Product	Jan	Feb
Apple	10	15
Pear	20	25

After

Product	Attribute	Value
Result appears after submission

2 Pivot Prediction

Tall → Wide + Aggregate

Source data

Store	Metric	Value
A	Sales	30
A	Sales	20
A	Costs	8
A	Costs	12
B	Sales	25
B	Sales	20
B	Costs	8
B	Costs	10

Pivot settings

Pivot column: Metric

Values column: Value

Aggregate: Sum

Store is the remaining column, so it becomes the row identifier.

Predict the output before running Pivot.

Option A

Store	Sales	Costs
A	50	20
B	45	18

Option B

Sales	Costs
95	38

Option C

Store	Metric	Value
A	Sales	50
A	Costs	20
B	Sales	45
B	Costs	18

Store stays in rowsMetric becomes headersSum duplicatesFill pivot cells

Source pattern

Store	Metric	Value
A	Sales	30
A	Sales	20
A	Costs	8
A	Costs	12

Pivot result

Store	Sales	Costs
Result appears after submission

3 Group By Prediction

Detail → Summary

Source data

Region	Amount
North	100
North	50
North	200
South	80
South	120

Group By settings

Group by: Region

New column: Total Amount

Operation: Sum

Column: Amount

What summary will Group By create?

Option A

Region	Total Amount
North	350
South	200

Option B

Total Amount
550

Option C

Region	Count
North	3
South	2

Find matching Region rowsForm groupsSum Amount

Detail rows

Region	Amount
North	100
North	50
North	200
South	80
South	120

Grouped result

Region	Total Amount
Result appears after submission

Hands-On Excel Practice

Build the transformation in real Power Query

The simulations are finished. Now use a compact practice workbook and perform the complete workflow in Excel yourself: Unpivot, Pivot, Unpivot again, Pivot again, then finish with Advanced Group By.

Practice workbook

Power_Query_Practice_Workbook.xlsx

The workbook is embedded in this page. It contains two raw Excel tables plus a data dictionary. Do not reshape the source sheets manually. Perform the work in Power Query.

Guided data: 32 rows Independent data: 36 rows 2 Pivot + 2 Unpivot workflows

Open the downloaded file in desktop Excel.

Rules for this practice

Work in Power Query. Do not solve the task with worksheet formulas or a PivotTable.

Keep the raw source unchanged. Your transformation should be repeatable when the source refreshes.

Use the specified reshape operations. The purpose is to practice Pivot, Unpivot, and Group By.

Check row counts. Each checkpoint below tells you whether your table shape is still correct.

Practice 1 · Guided

Operating Expense Reshape

Start: 32 rows → Finish: 12 rows

A regional controller receives one row per Store × Department × Account × Scenario. Monthly Amount and Transaction measures are spread across twelve columns. Build a reusable monthly management summary.

Starting source

Raw operating expense export

Complete source dataset: all 32 of 32 rows from the embedded workbook are included below. Scroll within the table or choose Open full table to inspect every row and column.

Region	Store_ID	Store_Name	Department	Account_Code	Account_Name	Scenario	Jan_Amount	Jan_Transactions	Feb_Amount	Feb_Transactions	Mar_Amount	Mar_Transactions
West	W01	Seattle	Operations	6100	Facility	Actual	$5,180	11	$5,400	12	$5,620	13
West	W01	Seattle	Operations	6100	Facility	Budget	$5,050	10	$5,270	11	$5,490	12
West	W01	Seattle	Operations	6200	Supplies	Actual	$5,630	12	$5,850	13	$6,070	14
West	W01	Seattle	Operations	6200	Supplies	Budget	$5,500	11	$5,720	12	$5,940	13
West	W01	Seattle	Sales	7100	Travel	Actual	$6,080	14	$6,300	15	$6,520	16
West	W01	Seattle	Sales	7100	Travel	Budget	$5,950	13	$6,170	14	$6,390	15
West	W01	Seattle	Sales	7200	Promotions	Actual	$6,530	15	$6,750	16	$6,970	17
West	W01	Seattle	Sales	7200	Promotions	Budget	$6,400	14	$6,620	15	$6,840	16
West	W02	Portland	Operations	6100	Facility	Actual	$5,530	13	$5,750	14	$5,970	15
West	W02	Portland	Operations	6100	Facility	Budget	$5,400	12	$5,620	13	$5,840	14
West	W02	Portland	Operations	6200	Supplies	Actual	$5,980	14	$6,200	15	$6,420	16
West	W02	Portland	Operations	6200	Supplies	Budget	$5,850	13	$6,070	14	$6,290	15
West	W02	Portland	Sales	7100	Travel	Actual	$6,430	16	$6,650	17	$6,870	18
West	W02	Portland	Sales	7100	Travel	Budget	$6,300	15	$6,520	16	$6,740	17
West	W02	Portland	Sales	7200	Promotions	Actual	$6,880	17	$7,100	18	$7,320	19
West	W02	Portland	Sales	7200	Promotions	Budget	$6,750	16	$6,970	17	$7,190	18
East	E01	Spokane	Operations	6100	Facility	Actual	$5,880	15	$6,100	16	$6,320	17
East	E01	Spokane	Operations	6100	Facility	Budget	$5,750	14	$5,970	15	$6,190	16
East	E01	Spokane	Operations	6200	Supplies	Actual	$6,330	16	$6,550	17	$6,770	18
East	E01	Spokane	Operations	6200	Supplies	Budget	$6,200	15	$6,420	16	$6,640	17
East	E01	Spokane	Sales	7100	Travel	Actual	$6,780	18	$7,000	19	$7,220	20
East	E01	Spokane	Sales	7100	Travel	Budget	$6,650	17	$6,870	18	$7,090	19
East	E01	Spokane	Sales	7200	Promotions	Actual	$7,230	19	$7,450	20	$7,670	21
East	E01	Spokane	Sales	7200	Promotions	Budget	$7,100	18	$7,320	19	$7,540	20
East	E02	Boise	Operations	6100	Facility	Actual	$6,230	17	$6,450	18	$6,670	19
East	E02	Boise	Operations	6100	Facility	Budget	$6,100	16	$6,320	17	$6,540	18
East	E02	Boise	Operations	6200	Supplies	Actual	$6,680	18	$6,900	19	$7,120	20
East	E02	Boise	Operations	6200	Supplies	Budget	$6,550	17	$6,770	18	$6,990	19
East	E02	Boise	Sales	7100	Travel	Actual	$7,130	20	$7,350	21	$7,570	22
East	E02	Boise	Sales	7100	Travel	Budget	$7,000	19	$7,220	20	$7,440	21
East	E02	Boise	Sales	7200	Promotions	Actual	$7,580	21	$7,800	22	$8,020	23
East	E02	Boise	Sales	7200	Promotions	Budget	$7,450	20	$7,670	21	$7,890	22

Final table

Management summary after Group By

Complete final result: all 12 of 12 rows. One row per Region × Department × Month, with the four measures summed across stores and accounts.

Region	Department	Month	Actual_Amount	Budget_Amount	Actual_Transactions	Budget_Transactions
West	Operations	Jan	$22,320	$21,800	50	46
West	Operations	Feb	$23,200	$22,680	54	50
West	Operations	Mar	$24,080	$23,560	58	54
West	Sales	Jan	$25,920	$25,400	62	58
West	Sales	Feb	$26,800	$26,280	66	62
West	Sales	Mar	$27,680	$27,160	70	66
East	Operations	Jan	$25,120	$24,600	66	62
East	Operations	Feb	$26,000	$25,480	70	66
East	Operations	Mar	$26,880	$26,360	74	70
East	Sales	Jan	$28,720	$28,200	78	74
East	Sales	Feb	$29,600	$29,080	82	78
East	Sales	Mar	$30,480	$29,960	86	82

Step-by-step directions

Load the source table

Open Practice_Expense_Export. Click anywhere inside ExpenseExportTable, then choose Data → From Table/Range. Rename the query Expense_Practice.

Checkpoint: 32 rows and 13 columns.

Unpivot all monthly measure columns

Select the seven identifier columns Region, Store_ID, Store_Name, Department, Account_Code, Account_Name, Scenario. Choose Transform → Unpivot Columns → Unpivot Other Columns.

The six monthly measure columns become two columns named Attribute and Value.

Checkpoint: 192 rows and 9 columns.

Split the combined month/measure label

Select Attribute. Choose Transform → Split Column → By Delimiter. Use underscore _ as the delimiter and split at the left-most delimiter. Rename the two new columns Month and Measure.

You should now see Month values such as Jan and Feb, and Measure values such as Amount and Transactions.

Checkpoint: still 192 rows.

Pivot Measure into Amount and Transactions

Select Measure and choose Transform → Pivot Column. Use Value as the Values Column. Under Advanced options choose Don't Aggregate.

The values Amount and Transactions become separate columns.

Checkpoint: 96 rows. You should have separate Amount and Transactions columns.

Unpivot Amount and Transactions again

Select the Amount and Transactions columns. Choose Transform → Unpivot Columns. Rename the new Attribute column to Measure. Leave the numeric column named Value.

Checkpoint: 192 rows again.

Create the Pivot key

Select Scenario first and Measure second. Choose Transform → Merge Columns. Use underscore _ as the separator and name the new column Scenario_Measure.

You should see four possible labels: Actual_Amount, Actual_Transactions, Budget_Amount, Budget_Transactions.

Pivot Scenario_Measure into four analysis columns

Select Scenario_Measure and choose Transform → Pivot Column. Use Value as the Values Column and choose Don't Aggregate.

You now have one row per Store × Department × Account × Month with four analysis columns.

Checkpoint: 48 rows.

Finish with Advanced Group By

Choose Home → Group By, then switch to Advanced. Group by Region, Department, and Month. The order of the grouping fields does not change the grouping result.

Add four aggregations, all using Sum:

Actual_Amount → Sum of Actual_Amount
Budget_Amount → Sum of Budget_Amount
Actual_Transactions → Sum of Actual_Transactions
Budget_Transactions → Sum of Budget_Transactions

Final checkpoint: 12 rows and 7 columns.

Set types, sort, and load

Set Amount columns to an appropriate numeric/currency type and Transaction columns to Whole Number. Sort Region, Department, and Month as desired. Choose Home → Close & Load to return the summary to Excel.

Self-check after you finish

Your final query should contain 12 rows.

For West · Operations · Jan: Actual Amount = $22,320; Budget Amount = $21,800; Actual Transactions = 50; Budget Transactions = 46.

Grand totals across the final table: Actual Amount = $316,800; Budget Amount = $310,560; Actual Transactions = 816; Budget Transactions = 768.

Practice 2 · Independent Transfer

Sales Performance Reshape

Start: 36 rows → Finish: 18 rows

Use the same reshape logic on a different business problem. The source contains Revenue and Units by month, Channel, Product Family, Store, and Scenario.

Goal

Create one row per Region × Channel × Month with Actual_Revenue, Budget_Revenue, Actual_Units, Budget_Units.

Starting source

Raw sales performance export

Complete source dataset: all 36 of 36 rows from the embedded workbook are included below. Scroll within the table or choose Open full table to inspect every row and column.

Region	Store_ID	Store_Name	Channel	Product_Code	Product_Family	Scenario	Jan_Revenue	Jan_Units	Feb_Revenue	Feb_Units	Mar_Revenue	Mar_Units
West	S01	Seattle	In Store	P100	Accessories	Actual	$8,300	83	$8,800	88	$9,300	93
West	S01	Seattle	In Store	P100	Accessories	Budget	$8,100	81	$8,600	86	$9,100	91
West	S01	Seattle	In Store	P200	Hardware	Actual	$9,500	95	$10,000	100	$10,500	105
West	S01	Seattle	In Store	P200	Hardware	Budget	$9,300	93	$9,800	98	$10,300	103
West	S01	Seattle	In Store	P300	Services	Actual	$10,700	107	$11,200	112	$11,700	117
West	S01	Seattle	In Store	P300	Services	Budget	$10,500	105	$11,000	110	$11,500	115
West	S01	Seattle	Online	P100	Accessories	Actual	$9,100	91	$9,600	96	$10,100	101
West	S01	Seattle	Online	P100	Accessories	Budget	$8,900	89	$9,400	94	$9,900	99
West	S01	Seattle	Online	P200	Hardware	Actual	$10,300	103	$10,800	108	$11,300	113
West	S01	Seattle	Online	P200	Hardware	Budget	$10,100	101	$10,600	106	$11,100	111
West	S01	Seattle	Online	P300	Services	Actual	$11,500	115	$12,000	120	$12,500	125
West	S01	Seattle	Online	P300	Services	Budget	$11,300	113	$11,800	118	$12,300	123
Central	S02	Denver	In Store	P100	Accessories	Actual	$9,600	93	$10,100	98	$10,600	103
Central	S02	Denver	In Store	P100	Accessories	Budget	$9,400	91	$9,900	96	$10,400	101
Central	S02	Denver	In Store	P200	Hardware	Actual	$10,800	105	$11,300	110	$11,800	115
Central	S02	Denver	In Store	P200	Hardware	Budget	$10,600	103	$11,100	108	$11,600	113
Central	S02	Denver	In Store	P300	Services	Actual	$12,000	117	$12,500	122	$13,000	127
Central	S02	Denver	In Store	P300	Services	Budget	$11,800	115	$12,300	120	$12,800	125
Central	S02	Denver	Online	P100	Accessories	Actual	$10,400	101	$10,900	106	$11,400	111
Central	S02	Denver	Online	P100	Accessories	Budget	$10,200	99	$10,700	104	$11,200	109
Central	S02	Denver	Online	P200	Hardware	Actual	$11,600	113	$12,100	118	$12,600	123
Central	S02	Denver	Online	P200	Hardware	Budget	$11,400	111	$11,900	116	$12,400	121
Central	S02	Denver	Online	P300	Services	Actual	$12,800	125	$13,300	130	$13,800	135
Central	S02	Denver	Online	P300	Services	Budget	$12,600	123	$13,100	128	$13,600	133
East	S03	Boston	In Store	P100	Accessories	Actual	$10,900	103	$11,400	108	$11,900	113
East	S03	Boston	In Store	P100	Accessories	Budget	$10,700	101	$11,200	106	$11,700	111
East	S03	Boston	In Store	P200	Hardware	Actual	$12,100	115	$12,600	120	$13,100	125
East	S03	Boston	In Store	P200	Hardware	Budget	$11,900	113	$12,400	118	$12,900	123
East	S03	Boston	In Store	P300	Services	Actual	$13,300	127	$13,800	132	$14,300	137
East	S03	Boston	In Store	P300	Services	Budget	$13,100	125	$13,600	130	$14,100	135
East	S03	Boston	Online	P100	Accessories	Actual	$11,700	111	$12,200	116	$12,700	121
East	S03	Boston	Online	P100	Accessories	Budget	$11,500	109	$12,000	114	$12,500	119
East	S03	Boston	Online	P200	Hardware	Actual	$12,900	123	$13,400	128	$13,900	133
East	S03	Boston	Online	P200	Hardware	Budget	$12,700	121	$13,200	126	$13,700	131
East	S03	Boston	Online	P300	Services	Actual	$14,100	135	$14,600	140	$15,100	145
East	S03	Boston	Online	P300	Services	Budget	$13,900	133	$14,400	138	$14,900	143

Final table

Region × Channel × Month summary

Complete expected shape: all 18 of 18 final rows are shown. Your Power Query result should contain these seven columns after the final Group By.

Region	Channel	Month	Actual_Revenue	Budget_Revenue	Actual_Units	Budget_Units
West	In Store	Jan	$28,500	$27,900	285	279
West	In Store	Feb	$30,000	$29,400	300	294
West	In Store	Mar	$31,500	$30,900	315	309
West	Online	Jan	$30,900	$30,300	309	303
West	Online	Feb	$32,400	$31,800	324	318
West	Online	Mar	$33,900	$33,300	339	333
Central	In Store	Jan	$32,400	$31,800	315	309
Central	In Store	Feb	$33,900	$33,300	330	324
Central	In Store	Mar	$35,400	$34,800	345	339
Central	Online	Jan	$34,800	$34,200	339	333
Central	Online	Feb	$36,300	$35,700	354	348
Central	Online	Mar	$37,800	$37,200	369	363
East	In Store	Jan	$36,300	$35,700	345	339
East	In Store	Feb	$37,800	$37,200	360	354
East	In Store	Mar	$39,300	$38,700	375	369
East	Online	Jan	$38,700	$38,100	369	363
East	Online	Feb	$40,200	$39,600	384	378
East	Online	Mar	$41,700	$41,100	399	393

1. Load: Open Challenge_Sales_Export and load SalesExportTable into Power Query. Start with 36 rows.
2. First Unpivot: Keep Region through Scenario as identifiers and use Unpivot Other Columns. You should have 216 rows.
3. Split: Split Attribute at _ into Month and Measure. Measure should contain Revenue and Units.
4. First Pivot: Pivot Measure using Value, with Don't Aggregate. You should have 108 rows and separate Revenue and Units columns.
5. Second Unpivot: Unpivot Revenue and Units. Rename Attribute to Measure. You should return to 216 rows.
6. Build key: Merge Scenario and Measure with _ to create Scenario_Measure.
7. Second Pivot: Pivot Scenario_Measure using Value and Don't Aggregate. You should have 54 rows.
8. Advanced Group By: Group by Region, Channel, Month. Sum Actual_Revenue, Budget_Revenue, Actual_Units, and Budget_Units. Your final result should have 18 rows.
9. Finish: Set data types, sort as desired, and Close & Load.

Independent challenge self-check

Final row count: 18.

For West · In Store · Jan: Actual Revenue = $28,500; Budget Revenue = $27,900; Actual Units = 285; Budget Units = 279.

Grand totals: Actual Revenue = $631,800; Budget Revenue = $621,000; Actual Units = 6,156; Budget Units = 6,048.