Advanced Excel · Interactive lesson 6 of 12
Power Query fundamentals
Formulas clean data one cell at a time. Power Query cleans it once, as a repeatable recipe — so next month's file goes through the exact same steps with one click, instead of a re-run of manual fixes.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialPower Query Basics: Combine and Clean Data in ExcelSkill check
Try these first:
- Import data from a file or folder with Get Data.
- Explain what an Applied Step is, and why the order of steps matters.
- Combine two files with the same columns into one table.
- Explain the difference between Refresh and Close & Load.
All familiar? Skip ahead to A7 — Data Model & Power Pivot Basics.
Concept teach
What you're learning
- Get Data from files/folders
- Point Power Query at a single file, or an entire folder — a folder query combines every file inside automatically, on every refresh, even ones added later.
- Applied Steps
- Every click — promote headers, change a type, remove a column — is recorded as a named, re-orderable, deletable step. That list is the recipe.
- Data types
- An imported column often arrives as generic text — set real types (Date, Whole Number) explicitly, since a "date" that's secretly text won't sort or filter like one.
- Split / merge / transform columns
- Split a full name by a delimiter, merge two columns into one key, or transform a column's case or format — each becomes its own Applied Step.
- Append vs. Merge queries
- Append stacks tables with the same columns into more rows (three months of the same export). Merge joins two tables by a matching column into more columns (like a lookup, but for whole tables).
- Refresh & load options
- Refresh re-runs every Applied Step against the current source file — the entire reason this beats a one-time manual cleanup.
Where to find it in Excel
Data›Get Data›From File › From Folder
| A | B | C | D | E | F | G | ||
|---|---|---|---|---|---|---|---|---|
| 1 | Month | Region | Sales | |||||
| 2 | 2026-07 | North | 6,900 | |||||
| 3 | 2026-07 | South | 5,400 | |||||
| 4 | ||||||||
| 5 | ||||||||
| 6 | ||||||||
| 7 | ||||||||
| 8 |
Swipe sideways to see the whole window.
- 1Data › Get Data is where every Power Query import starts.
- 2From File covers workbooks, CSV, JSON and PDF.
- 3From Folder combines every file in a folder; add a file and refresh.
- 4Launch Power Query Editor opens the Applied Steps, data types and Merge/Append.
- 5Queries & Connections lists loaded queries; Refresh All updates them.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided build
Two monthly export files below, each a little messy. Work through the query steps in order — each one appends to the Applied Steps list and updates the live preview.
Independent extension
Combine your own monthly files
Take a folder of CSV exports that share the same columns (or simulate one with a few copies of the same file), import the whole folder with Get Data, standardize column names and types, remove rows that error out, and load a single clean table. Refresh it after adding one more file to the folder and confirm it appears with no extra steps.
Spot a mistake or have an idea for this lesson? Send feedback