Power Query: Build a Repeatable Data Preparation Pipeline

Business scenario

The monthly sales export needs preparation before analysis

You receive a monthly sales export from an operating system. The file contains combined fields, inconsistent text, month columns spread across the worksheet, and coded region values. Your job is to build a preparation process that can be reused next month.

Think in two layers: Power Query prepares the data. Excel formulas, PivotTables, and charts can then analyze the prepared data.

Applied Steps

One reusable query

Each revealed task adds or reuses a recorded transformation step.

Source data

Start with Q1. The second file later adds Q2 columns so you can test refresh behavior.

Sales Data • Q1

Order_Info	Category	Region_ID	Jan_Sales	Feb_Sales	Mar_Sales
ORD101-smith	lapTOps	R01	1200	1500	1100
ORD102-JONES	TabLEts	R02	800	950	900
ORD103-davis	monitors	R01	450	500	480

Region Mapping

Region_ID	Region_Name
R01	North America
R02	Europe
R03	Asia Pacific
R04	Latin America

Updated source • Q2 columns arrive USED IN TASK 5

Order_Info	Category	Region_ID	Jan_Sales	Feb_Sales	Mar_Sales	Apr_Sales	May_Sales	Jun_Sales
ORD101-smith	lapTOps	R01	1200	1500	1100	1300	1400	1500

Five connected transformations

Problem → Predict → Reveal → Verify

Make a prediction first. Then reveal the Power Query step and inspect what changed.

Task 1 • Split a combined field

Separate Order ID from Salesperson

The source stores two pieces of information in one field: ORD101-smith.

Worksheet approach

Text formulas such as LEFT, RIGHT, FIND, or TEXTSPLIT can separate the values. That can be useful for worksheet calculations, but here the same imported field must be prepared repeatedly.

Prediction: what tells Power Query where to split?

Power Query transformation

Select Order_Info → Split Column → By Delimiter → Hyphen (-)

ORD101-smith

→

Order ID	Salesperson
ORD101	smith

Why it matters: the split becomes a recorded query step, so the same transformation can run again when the source is refreshed.

Task 2 • Clean inconsistent text

Standardize Category values

The source contains spaces and inconsistent capitalization such as lapTOps .

Worksheet approach

TRIM and PROPER can clean text in worksheet formulas. Power Query is useful when you want those cleanup steps recorded as part of the import process.

Prediction: which sequence best cleans the value?

Two recorded text transformations

Raw

lapTOps

↓ Trim

lapTOps

↓ Capitalize Each Word

Laptops

Applied Steps idea: Power Query records each transformation. On refresh, it reruns the sequence in order.

Task 3 • Reshape wide data

Turn month columns into analysis-ready rows

Jan, Feb, and Mar are repeated measures stored as separate columns. For many analyses, the month should become a field rather than part of the column name.

Order	Category	Region	Jan	Feb	Mar
ORD101	Laptops	R01	1200	1500	1100

Prediction: which columns should stay as identifiers?

Unpivot Other Columns

Select Order, Category, Region → right-click → Unpivot Other Columns

Interactive reshape

Click Unpivot and Pivot to see the structure change

Wide source: each month is stored as a separate column.

1. Wide
Jan, Feb, Mar are columns.

2. Unpivot
Month becomes a field and Sales becomes a value.

3. Pivot
Month values become columns again.

Why it matters: unpivoting creates an analysis-ready long table. Pivoting is the reverse operation when a wide presentation is needed. Students should understand both directions, not just memorize the menu command.

Task 4 • Combine related tables

Bring Region Name into the sales data

The sales table has Region_ID. The mapping table has Region_ID and Region_Name. The shared key lets Power Query match the records.

Sales

Region_ID
R01
R02

Region Mapping

Region_ID	Region_Name
R01	North America
R02	Europe

Prediction: which field should be selected in both tables for the merge?

Merge using a common key

Home → Merge Queries
Select Region_ID in both tables → choose Left Outer → OK → expand Region_Name.

Sales table: keep every sales row.

Common key: match Region_ID to Region_ID.

Expand: bring Region_Name into the result.

Region_ID	Region_Name
R01	North America
R02	Europe

Left Outer Join, in plain language: keep every sales row and bring in a matching region name when one exists.

Task 5 • Reuse the pipeline next month

Q2 columns arrive: Apr, May, and Jun

The query was designed with Unpivot Other Columns, so the month columns are treated as a changing group rather than individually hard-coded fields.

Jan	Feb	Mar	Apr	May	Jun
1200	1500	1100	1300	1400	1500

Prediction: after Refresh All, what should happen?

Refresh reruns the recorded pipeline

Data → Refresh All

Before

Jan • Feb • Mar

→

After refresh

Jan • Feb • Mar • Apr • May • Jun

Important: this works because the query was designed with Unpivot Other Columns and the source structure expands appropriately. Good query design makes refresh workflows more resilient, but Power Query does not automatically handle every possible structural change.

Complete the five transformations

After all five reveals, the comparison and final takeaway will unlock.

0/5 completed

When Should I Use Power Query?

Need	Excel formulas / worksheet tools	Power Query
Calculate values in cells	Excellent	Not its primary purpose
Interactive worksheet analysis	Excellent	Limited
Clean imported data repeatedly	Possible with maintained formulas and steps	Excellent
Reshape wide data	Possible	Excellent
Combine related tables	Worksheet lookup approaches can work	Merge is designed for this preparation workflow
Repeat the same cleaning next month	Requires maintaining worksheet logic	Refresh reruns recorded steps
Prepare imported data for analysis	Possible	Excellent

Excel calculates. Power Query prepares.

In real workflows, you will often use both: Power Query prepares the data, and Excel formulas, PivotTables, and charts analyze it.

What you just learned

Split

One combined field into multiple fields.

Transform

Dirty text into a consistent standard.

Unpivot

Repeated period columns into analysis-ready rows.

Merge

Related tables using a common key.

Refresh

Run the recorded pipeline again on updated data.