Power Query

Excel Advanced

Chapter 4 · Power Query: Importing, Cleaning & Reshaping Data From Multiple Sources

Every chapter so far assumed the data was already sitting cleanly in the worksheet. Power Query solves the problem that comes before that — getting messy, scattered, or badly-shaped data from wherever it actually lives (a CSV export, a folder of monthly files, another workbook) into a clean, consistent shape, and doing it in a way that can be replayed automatically every time the source data refreshes, rather than repeated by hand.

What Power Query Actually Is

Power Query (Data tab → Get Data, or Get & Transform Data) is a genuinely separate transformation engine bundled inside Excel. Its defining idea: every cleaning action you take — renaming a column, removing rows, splitting text — isn't typed as a formula at all. It's recorded as one numbered step in a list, and that whole list can be replayed automatically against tomorrow's version of the same source data.

Importing Data From Different Sources

Data → Get Data offers several source types, each opening the same Power Query Editor once connected:

From Text/CSV — a single delimited file.
From Workbook — pulling one sheet or table out of another Excel file.
From Folder — genuinely powerful: point at a folder of similarly-shaped files (e.g. twelve monthly export files) and Power Query combines all of them into one single table automatically, applying the same cleaning steps to every file.
From Web — extracting a table directly from a web page's URL.

The Applied Steps Panel: Recorded, Replayable Transformations

Inside the Power Query Editor, every click you make — renaming a column, removing a column, changing a data type, filtering out some rows — appears as a new entry in the Applied Steps panel on the right, in the exact order you performed them. Steps can be renamed, reordered, or deleted individually, and clicking any earlier step shows you a live preview of the data at exactly that point in the process. Under the hood, each step is actually a line of Power Query's own formula language (called "M") — but the Applied Steps panel means most day-to-day cleaning never requires writing M by hand at all.

Common Cleaning Transformations

Transformation	What it does
Change Data Type	Forces a column to be treated as genuine Number/Date/Text — fixes the exact "numbers stored as text" problem from Excel Fundamentals Chapter 9
Remove Duplicates	Removes duplicate rows, based on selected columns — unlike Excel's own Remove Duplicates, this step re-runs automatically on refresh
Split Column	Breaks one column into several, by a delimiter (comma, space) or by character count
Fill Down	Fills blank cells with the value from the cell above — common when a source export only writes a category name once, then leaves it blank for every following row
Trim / Clean	Removes leading/trailing spaces and non-printable characters — the classic fix for lookups (Chapter 5) that mysteriously fail to match
Unpivot Columns	Turns wide data (separate Jan/Feb/Mar columns) into tall data (one "Month" column, one "Value" column) — often the single most useful reshape for badly-formatted source exports

Combining Data from Multiple Sources: Append vs. Merge

Two genuinely different operations combine two queries, and picking the wrong one produces confusingly broken results:

Append Queries — stacks rows from two (or more) sources with the same columns on top of each other, like January's transactions plus February's transactions becoming one longer list. Row count grows; column count stays the same.
Merge Queries — joins two sources side by side, matching rows based on a shared key column, similar in spirit to Chapter 2's XLOOKUP but operating on entire tables at once rather than one value at a time. Column count grows; row count depends on the join kind chosen (Inner, Left Outer, Right Outer, Full Outer).

Loading the Result Back Into Excel

Close & Load (or Close & Load To…) sends the finished query's result somewhere useful: as a new Excel Table on a worksheet (immediately gaining every Chapter 7 Table benefit), as a connection only (kept available for other queries to Merge against, without cluttering the worksheet with its own visible table), or straight into the Data Model — the foundation Chapter 5's Power Pivot builds on directly.

Refreshing a Query

Data → Refresh All re-runs every recorded Applied Step against whatever the source currently contains — not just re-totalling numbers the way Excel Fundamentals Chapter 9's PivotTable refresh does, but re-importing, re-cleaning, and re-reshaping the data completely from scratch, automatically, every time.

Loading a query as a Table stacks Chapter 7's benefits on top of Power Query's own

Choosing "New Worksheet" (as a Table) when you Close & Load gives you both worlds at once: Power Query's repeatable import-and-clean pipeline feeding data in, and Chapter 7's auto-expanding Table (with structured references) holding it once it arrives — a formula like =AVERAGE(CleanedSales[Amount]) stays correct through both a query refresh AND any further rows the Table itself might gain.

A Merge's join key must match exactly, and a renamed source column breaks the recorded steps

Merging two queries on a key column that differs by case, extra whitespace, or formatting (Chapter 6's number-format lesson applies here too) silently produces unmatched rows with blank results, rather than an error — the same exact-match discipline Chapter 5's lookup functions required. Separately, if a source file's column is later renamed at the source, any Applied Step referencing that column's old name breaks on the next refresh with a genuine error — unlike an Excel Table's own forgiving auto-expansion (Chapter 7), Power Query's recorded steps are tied to the exact column names and structure that existed when they were first recorded.

Hands-On Exercises

Exercise 1

A folder contains 12 CSV files, one per month, each with identical columns: Date, Category, Amount. Describe exactly which Get Data option combines all 12 into a single table automatically, and explain what happens the next time this query is refreshed after a 13th monthly file is added to the same folder.

📄 View solution

Exercise 2

An imported source table has one row per sale but a wide layout — one column per month (Jan, Feb, Mar, ... Dec), each holding that month's sales figure for a given Product. You need it reshaped into a tall format instead: one row per Product-Month combination, with a "Month" column and a "Sales" column. Which Power Query transformation solves this directly, and what would the reshaped output's columns be?

📄 View solution

Exercise 3

You need to combine a Products query (columns: ProductID, ProductName, Category) with a Sales query (columns: ProductID, Date, Amount) so each sale row also shows its product's name and category. Is this an Append or a Merge, and why? Then describe a realistic reason this operation could silently produce blank ProductName/Category values for some rows even though every ProductID genuinely exists in both queries.

📄 View solution

Chapter 4 Quick Reference

Power Query — a separate transformation engine; cleaning actions are recorded as replayable steps, not typed formulas
From Folder — combines every similarly-shaped file in a folder into one table, and picks up new files automatically on refresh
Applied Steps — the recorded, editable, reorderable list of every transformation performed
Unpivot Columns — turns wide data (one column per category) into tall data (one row per category)
Append stacks rows (same columns, more rows); Merge joins on a key (same rows, more columns) — the two are not interchangeable
Refresh All replays the entire import-and-clean pipeline, not just a re-total — a genuinely bigger operation than a PivotTable's own refresh
A Merge's join key needs an exact match, and a renamed source column breaks recorded steps on the next refresh