Week 4: Power Query & Power Pivot — Modeling Data at Scale

1. What Power Query Actually Does

Power Query is Excel's built-in ETL tool — Extract data from a source (a CSV, a folder of files, a database, another sheet), Transform it (rename, filter, split, merge columns), then Load the cleaned result into the workbook. The key difference from doing this manually: every transformation is recorded as a named, reorderable step, not a one-time edit.

getting data in

Data tab -> Get Data -> From File / From Database / From Folder
-> select your source -> Power Query Editor opens automatically

The Power Query Editor shows the data on one side and an Applied Steps list on the other — every click you make (remove a column, filter a row, split a column by delimiter) appears there as a named, editable step, not a change baked permanently into the data. This is the entire point of the tool: the cleanup is a recipe, not a one-off edit.

2. Shaping Data: Applied Steps

Common shaping operations, all available from the ribbon with no formula typed at all:

typical cleanup steps, in order

1. Remove Columns -> drop anything you don't need (right-click column header)
2. Remove Rows -> Remove Blank Rows, or filter out a header row repeated
   mid-file (common in exports stitched from multiple sheets)
3. Change Type -> force a column that imported as Text to actually be a
   Date or Whole Number (this alone fixes a huge share of "my SUM shows 0" bugs)
4. Split Column -> By Delimiter -> turn "Smith, John" into two columns
5. Trim / Clean -> the Power Query equivalent of Week 1's TRIM() formula,
   applied to an entire column in one click

Every one of these appears in the Applied Steps list by name — click any earlier step to see the data exactly as it was at that point, or delete a step entirely if a transformation turns out to be wrong. When you're done, Close & Load brings the result into the workbook as a proper table.

"Change Type" fixes more bugs than it looks like it should

A column imported from a CSV as text looks identical to a real number in the cell, but SUM silently treats every "text number" as 0. If a total looks wrong after an import, check each column's data type in Power Query before assuming a formula is broken.

3. Merging & Appending Queries

Two different operations, easy to mix up by name: Merge combines two queries side by side by matching a key column (the Power Query equivalent of a SQL join, covered fully next week); Append stacks two queries with the same columns on top of each other, row after row.

merge: side by side, matched by a key

Home -> Merge Queries -> pick Orders and Products -> select "product_id" in
both -> choose "Left Outer" -> expand the new column to pull in product_name
and price, exactly like the XLOOKUP from last week, but reusable and refreshable

append: stacked, row after row

Home -> Append Queries -> combine "Orders_Jan", "Orders_Feb", "Orders_Mar"
(three separate monthly export files, same columns) into one continuous table

A useful rule for telling them apart at a glance: merge makes the table wider (new columns from a second table), append makes it taller (more rows, same columns). Append is exactly what solves "I get a new export file every month and don't want to copy-paste it into last month's data by hand."

4. Power Pivot: A Data Model Across Tables

Power Pivot takes several cleaned tables and connects them with relationships — instead of merging everything into one giant flat table, related tables stay separate but linked, exactly like a SQL database's tables connected by foreign keys (next week's topic).

building a data model

1. When loading each Power Query result, check "Add this data to the Data Model"
   instead of (or in addition to) loading it as a worksheet table
2. Data tab -> Manage Data Model -> Diagram View
3. Drag "product_id" from Orders onto "product_id" in Products to create the
   relationship -- Power Pivot draws a connecting line, exactly like an ER
   diagram for a relational database
4. Build a PivotTable from the Data Model (not a single sheet) -- fields from
   BOTH tables are now available in the same field list

This is the direct Excel equivalent of the relational model from Week 2 — instead of denormalizing everything into one wide sheet (which duplicates the product name on every single order row), the model keeps Products and Orders separate and joins them only when a report actually needs both, which scales to far more data and far more tables than one flat sheet ever could.

5. Refreshable, Repeatable Reports

The entire payoff of doing cleanup in Power Query instead of by hand: next week's export file gets the exact same treatment automatically, with a single click.

the refresh workflow

1. Drop this week's new raw export into the same file path/name the query
   already points at
2. Data tab -> Refresh All
3. Every Applied Step re-runs against the new data -- cleanup, merges,
   type changes, everything -- and every PivotTable built on the model updates too

A report built this way survives a new week of raw data without any rework at all — the alternative, manually redoing lookups and PivotTables against a fresh copy-paste every single week, is exactly the repetitive work Power Query exists to eliminate. This is also the direct bridge into Power BI (Weeks 9–12), which uses this same Power Query engine underneath its own data-import step.

6. Hands-on Exercise

Hands-on

Build a refreshable, multi-table sales model

Rebuild Week 3's dashboard data using Power Query and Power Pivot instead of manual lookups.

Requirements:

Save your Products and Orders data (from Week 3) as two separate CSV files, then import each into Excel via Get Data -> From File -> From Text/CSV, loading both into the Data Model.
In Power Query, add at least 2 cleanup steps to Orders (e.g. Change Type on order_date, Trim on region) and confirm they appear in Applied Steps.
In the Data Model's Diagram View, create a relationship between Orders.product_id and Products.product_id.
Build a PivotTable from the Data Model showing total revenue by region, pulling product_name from Products and qty/price from Orders — with no XLOOKUP anywhere.
Add 3 new rows to the original Orders CSV file outside Excel, then use Refresh All and confirm the PivotTable updates to include them.

Hint

If the Diagram View shows two disconnected tables with no line between them, double-check both product_id columns actually have the exact same data type (Text vs. Whole Number) — a type mismatch is the single most common reason a relationship silently fails to auto-detect.

7. Knowledge Check

Four quick questions. Expand each to check your answer.

What does the Applied Steps list in the Power Query Editor actually record?

Every transformation applied to the data — removing a column, changing a type, splitting a column, filtering rows — as a named, ordered, editable step, rather than a permanent one-time edit to the raw data. Any step can be clicked to preview the data at that point, or deleted if a transformation turns out to be wrong.

You have three monthly export files with identical column headers. Should you Merge or Append them?

Append. Merge combines tables side by side by matching a key column, adding new columns (making the table wider) — the right tool when two tables describe different things about the same entity. Append stacks tables with the same columns on top of each other, adding new rows (making the table taller) — the right tool for three files that are really just three months of the same data.

Why keep Products and Orders as two separate, related tables in a data model instead of merging them into one wide table?

Merging them into one flat table duplicates every product's name and price onto every single order row for that product — wasteful, and a source of inconsistency if the price is later corrected in only one of those many duplicated rows. Keeping them separate but related mirrors a relational database: each product's details live in exactly one place, and the relationship joins them together only when a report actually needs both.

After clicking Refresh All with a new week of raw data, does a PivotTable built from the Data Model need to be rebuilt?

No. Refreshing re-runs every Applied Step in every query against the new data automatically, and any PivotTable built on the resulting Data Model updates to reflect it — that's the entire point of building the report this way instead of manually recreating lookups and PivotTables against each new copy-pasted file.