Power Query: Transform Anything

What Power Query Solves

The monthly report problem. Every month you receive a CSV export. You delete the first 3 rows, fix the date format, remove duplicates, merge with another file, and paste the result into your workbook. Power Query records these steps once and replays them forever. Next month: one click on Refresh.

The multi-source problem. Data lives in CSV files, web pages, databases, SharePoint lists, and folders full of Excel files. Power Query connects to all of them through a unified interface, combining data that would otherwise require manual assembly.

The "my colleague reformatted the export" problem. Power Query steps are resilient to minor changes in source data. Column names matter; column positions do not. As long as the column names stay consistent, your transformation pipeline keeps working.

The Power Query Workflow

1. Get Data (Data tab → Get Data) — choose source: Excel file, CSV, web URL, folder, SharePoint, SQL database, or dozens of other connectors
2. Transform in the Power Query Editor — a visual interface where every action you take becomes a step in the Applied Steps panel
3. Load — to a worksheet, to a pivot table data source, or to the Data Model

Every step in the Applied Steps panel is recorded as M code (the underlying Power Query language). You can click back to any step to see the data at that point in the pipeline, and you can reorder, rename, or delete steps.

Essential Transformations

Removing Rows and Filtering

• Remove Top Rows — eliminate header rows that are not column headers (report titles, metadata rows)
• Remove Duplicates — keep only unique rows based on selected columns
• Filter Rows — show only rows matching a condition (the same as Excel filtering, but recorded as a repeatable step)

Setting Data Types

This is the transformation most people forget and later regret. Setting column types explicitly in Power Query prevents silent errors:

• Text numbers (IDs stored as text) being calculated as zero
• Dates stored as numbers rather than date values
• Decimal numbers that Excel misidentifies as text

Right-click a column header → Change Type → select the correct type. Do this for every column before loading.

Splitting and Merging Columns

Split by delimiter: "Last, First" → Last column and First column. Split Column → By Delimiter → select comma, space, or custom character.

Merge columns: combine First Name and Last Name into a Full Name column. Add Column → Merge Columns → specify delimiter.

Merge Queries (Power Query Joins)

The Power Query equivalent of XLOOKUP/VLOOKUP. Join two tables on a shared column.

Home → Merge Queries → select the second table, the join columns, and the join type:

• Left Outer — all rows from the left table, matching data from right (equivalent to XLOOKUP returning blanks for no match)
• Inner — only rows with matches in both tables
• Full Outer — all rows from both tables, blanks where no match

After merging, expand the joined table column to show the columns you want. This is more reliable than lookup formulas for combining large datasets because it handles duplicates and many-to-many relationships correctly.

Append Queries (Stacking Tables)

Combine multiple tables vertically. January data + February data + March data = year-to-date table.

Home → Append Queries → select tables to combine. Works with entire folders: Get Data → From Folder → select folder → combine all files. Power Query reads every file in the folder, applies the same transformation, and stacks the results. Add a new monthly file to the folder, hit Refresh, done.

Unpivot Columns

The transformation that makes wide data pivot-table-ready. Wide format has one column per time period:

Tall (unpivoted) format has one row per data point:

Select the month columns → Transform → Unpivot Columns. The result is a proper relational table that pivot tables and SUMIFS can aggregate correctly.

The M Language

Power Query steps are recorded in M (a functional language). You rarely need to write M from scratch, but understanding the basics helps when you need to customize a step.

The Advanced Editor (View → Advanced Editor) shows the complete M code for your query. Common patterns:

Table.SelectRows(Source, each [Status] = "Active")
Table.AddColumn(Source, "Margin", each [Revenue] - [Cost])
Table.TransformColumnTypes(Source, {{"Date", type date}})

For error handling within transformations:

try [Column] otherwise null

This replaces error values in a column with null instead of failing the entire query refresh.

Real-World Patterns

Consolidate Monthly Reports

1. Get Data → From Folder → point at the folder containing monthly CSV exports
2. Power Query shows a list of all files
3. Transform: filter to .csv files, combine, apply your transformations
4. Load to worksheet

Next month: add the new CSV to the folder → Refresh. Zero manual work.

Clean Messy Source Data

Common issues Power Query handles cleanly:

• Trim and clean whitespace from text columns
• Standardize date formats from regional variations
• Replace inconsistent status values ("active" / "Active" / "ACTIVE") with canonical values
• Fill down blank cells in merged-header exports

Web Data

Get Data → From Web → paste a URL. Power Query extracts tables from the HTML automatically. Works for public pricing pages, government data portals, financial data tables. Set to refresh daily for near-live data feeds.

Power Query and AI Automation

Power Query is Excel's native automation engine. The Extract-Transform-Load (ETL) thinking it requires is the same thinking behind n8n workflows, Make scenarios, and data engineering pipelines. Master Power Query and you will understand the core concepts of data automation — without writing a line of code.

See [Dashboards, Copilot & AI-Powered Analysis](/tutorials/excel-mastery/dashboards-copilot-ai) for how to combine Power Query-prepared data with AI analysis tools.