Excel Power Query: Complete Beginner's Guide
Power Query records every data cleaning step you take — then reruns the whole process automatically on fresh data. It replaces hours of manual copy-paste and formula work every week.
The Core Idea
Power Query is a recorded macro for data cleaning. You clean the data once through a visual editor — each action becomes a step. Click Refresh and every step reruns instantly on the latest source data. No formulas, no copy-paste, no manual work on the second run.
Data Sources You Can Connect To
Data → From Text/CSVBest way to import CSV — handles encoding and delimiter detection better than double-clicking.
Data → From Table/RangeFormat your data as a Table first; the query auto-expands when new rows are added.
Data → Get Data → From File → From FolderCombine all CSV/Excel files in a folder into one table — great for monthly report consolidation.
Data → From WebScrape a table from any public web page. Paste the URL and Power Query finds the tables on the page.
Data → Get Data → From DatabaseDirect SQL Server, MySQL, PostgreSQL connection — write SQL or use the visual query builder.
Data → Get Data → From Online ServicesImport from SharePoint lists or OneDrive Excel files — ideal for team data sources.
How Power Query Works: The Applied Steps Panel
Every action in the Power Query editor creates an "Applied Step" in the right-hand panel. These steps run in order from top to bottom, transforming the data progressively. You can click any step to see what the data looked like at that point, rename steps, delete them, or insert new ones between existing steps.
This recorded sequence is stored in the workbook — not in a separate file. When you click Refresh, Power Query connects to the source, pulls the latest data, and applies every step in sequence automatically.
The Basic Workflow
- Connect — Data → Get Data → choose your source
- Transform — use the Power Query editor to clean and shape the data
- Load — Home → Close & Load → loads the result into Excel as a Table
- Refresh — right-click the output table → Refresh, or Data → Refresh All
10 Most Useful Transformation Steps
Remove columns
Right-click column header → Remove. Or select the columns you WANT and use Remove Other Columns.
Rename columns
Double-click the column header and type the new name.
Change data type
Click the type icon (ABC, 123, calendar) on the left of the column header. Always set types explicitly — do not rely on auto-detection.
Filter rows
Click the column header dropdown → filter by value, condition, or text. Same UI as Excel filters.
Split a column
Right-click → Split Column → By Delimiter. Splits "First Last" into two columns at the space.
Remove duplicates
Select the key columns, right-click → Remove Duplicates. Keeps the first occurrence.
Fill down blank cells
Right-click a column → Fill → Down. Fills each blank with the value from the row above.
Unpivot columns
Select the category columns, right-click → Unpivot Other Columns. Converts wide format to long format.
Merge queries (lookup)
Home → Merge Queries. Choose both tables and the matching column — equivalent to a VLOOKUP join.
Append queries (stack rows)
Home → Append Queries. Stacks two tables vertically — equivalent to combining two ranges.
Merge vs Append: The Two Ways to Combine Tables
Merge (horizontal join) combines two tables side by side based on a matching column — the equivalent of VLOOKUP. Choose the join type: Left Outer (keep all rows from the left table), Inner (only matching rows), Full Outer (all rows from both). Use Merge when you want to add columns from one table to another.
Append (vertical stack) places two tables on top of each other. Use Append when both tables have the same columns and you want to combine their rows — for example, stacking monthly sales files into one annual table.
Combining a Folder of Files
One of Power Query's most powerful features: Data → Get Data → From File → From Folder. Point it at a folder containing CSV or Excel files with the same structure. Power Query loads all of them, stacks the rows, and adds a column with the source filename. Every month, drop a new file in the folder and click Refresh — it is included automatically.
Load Options: Table, Connection Only, or Data Model
- Load to Table — puts the result in an Excel sheet as a Table. The most common option.
- Load to Data Model — loads into Power Pivot for multi-table modelling and DAX measures. Use for large datasets or complex relationships.
- Connection Only — the query runs but does not write to a sheet. Use when this query feeds another query (via Merge or Append) rather than being an end result itself.
Errors in your Power Query output?
Data type mismatches and source errors show up as [Error] cells in the output table. The Spreadsheet Auditor flags all error cells with their location so you can trace them back to the query step that caused them.
Frequently Asked Questions
What is Power Query in Excel?
A data connection and transformation tool built into Excel 365, 2019, and 2016. It records cleaning steps and reruns them automatically on refresh — no formulas, no manual work on subsequent runs.
Where is Power Query in Excel?
Data tab → Get & Transform Data section. Click Get Data to see all sources, or use the quick buttons: From Text/CSV, From Web, From Table/Range.
What is the difference between Power Query and VLOOKUP?
VLOOKUP is a per-row formula that recalculates continuously. Power Query performs a full table join and loads results as a new table, refreshed on demand. Better for large tables, recurring processes, and clean output without formulas in every cell.
Related Guides
Excel Tables Guide
Format data as a Table before loading into Power Query — auto-expands on refresh.
Read →
Data Cleaning Hub
When to use Power Query vs formulas vs browser tools for cleaning tasks.
Read →
Pivot Table Guide
Combine Power Query (clean) + pivot table (summarise) for a robust reporting workflow.
Read →
Best Excel Tools
Power Query is free and built in — see what other tools complement it.
Read →