Transforming Data in Power Query

Getting data into Power Query is only the first step. The real work — and the real value — is in transforming it. Raw data almost never arrives in the shape you need: columns are in the wrong order, headers are missing or duplicated, data types are wrong, values are inconsistently formatted, and there are blank rows and irrelevant columns scattered throughout. Power Query's transformation tools handle all of this through a visual, step-by-step process where every action is recorded, repeatable, and completely reversible. This lesson covers the core transformations you will use in virtually every query you build.

The Applied Steps Pane — Your Transformation History

Every action you take in Power Query is automatically recorded as a step in the Applied Steps pane on the right side of the editor. Steps run in order from top to bottom, and you can click any step to see exactly what the data looked like at that point. You can rename steps, delete them, reorder them, and insert new ones anywhere in the chain.

Promoting Headers

When data is imported, Power Query sometimes treats the first row as data rather than column headers — naming columns Column1, Column2, and so on. Promoting headers lifts the first row into the header bar. This is almost always the first manual step in any query.

Changing Data Types

Every column in Power Query has a data type — Text, Whole Number, Decimal, Date, True/False, and so on. The type icon appears to the left of each column header: ABC for text, 123 for whole number, 1.2 for decimal, a calendar icon for date. Getting types right is critical because it controls how values sort, filter, and calculate. A sales column stored as Text will not sum. A date column stored as Text will not sort chronologically.

To change a type: click the type icon to the left of the column name, or right-click the column header → Change Type. You can also select multiple columns with Shift+click or Ctrl+click and change all their types at once.

Removing and Reordering Columns

Raw datasets almost always contain more columns than you need. Removing irrelevant columns early in the query keeps the output clean and reduces the amount of data loaded into Excel. There are two approaches — Remove Columns (keep what you explicitly remove) and Remove Other Columns (keep only what you select, remove everything else). Remove Other Columns is safer when columns might be added to the source later.

Right-click a column header:
  Remove Columns         →  deletes the selected column(s)
  Remove Other Columns   →  keeps only the selected columns, removes all others

Reorder columns:
  Drag column headers left or right in the preview pane
  Or: right-click → Move → To Beginning / To End / Before / After

Select multiple columns:
  Shift+click  →  select a contiguous range of columns
  Ctrl+click   →  select non-adjacent columns

A best practice tip: use Remove Other Columns rather than Remove Columns when building queries that will refresh against live or changing source data. If someone adds a new column to the source file later, Remove Other Columns ensures only your intended columns remain — Remove Columns only removes the ones you explicitly specified, and the unexpected new column will appear in your output.

Filtering Rows

Row filtering in Power Query works similarly to AutoFilter in Excel — click the dropdown arrow on a column header to filter by value, condition, or range. But unlike AutoFilter, filters in Power Query are recorded as steps and re-applied automatically on every refresh.

The most important row filters to know: filtering out blank rows (click the dropdown → uncheck null), filtering by a text condition (Contains is useful for partial matching), and filtering dates to a relative window like "In the Previous 12 Months" — this creates a self-updating date filter that always covers the last year without needing manual updates.

Renaming Columns

Column names in raw data are often technical, abbreviated, or simply unhelpful. Renaming them in Power Query produces clean, professional output headers without touching the source. Double-click any column header in the preview pane to rename it inline.

Adding Custom Columns

Custom columns let you add a new calculated column to the query using Power Query's M language. You access this via Add Column → Custom Column. The formula bar uses square bracket notation to reference existing columns — [Sales] rather than a cell reference. Most common calculations are straightforward even if you have never used M before.

Add Column → Custom Column → New column name → Formula

Full name from first and last name columns:
= [First Name] & " " & [Last Name]

Bonus at 10% of salary:
= [Salary] * 0.1

Sales vs target variance:
= [Sales] - [Target]

Year from a date column:
= Date.Year([Start Date])

Categorise salary bands:
= if [Salary] >= 70000 then "Senior"
  else if [Salary] >= 50000 then "Mid"
  else "Junior"

Splitting Columns

Power Query's Split Column feature is the point-and-click version of what TEXTSPLIT does in formulas. Right-click a column header → Split Column, then choose your method — by delimiter, by number of characters, by position, or by transition from digit to letter. The result is multiple new columns, each containing one segment of the original value.

Replace Values and Trim Whitespace

Two of the most common data cleaning operations in Power Query are replacing specific values and removing extra whitespace. Both are in the Transform menu or accessible via right-click on a column.

Replace Values:
  Right-click column → Replace Values
  Value to find: "N/A"   →  Replace with: null
  Value to find: "GB"    →  Replace with: "United Kingdom"

Useful for: fixing inconsistent category names, replacing error strings with null,
standardising abbreviations across a column.

Trim (remove leading/trailing spaces):
  Select column → Transform → Format → Trim

Clean (remove non-printable characters):
  Select column → Transform → Format → Clean

Trim + Clean together — handles most whitespace issues from exported data:
  Select column → Transform → Format → Trim
  Then immediately → Transform → Format → Clean

Leading and trailing spaces are one of the most common invisible data quality issues. A cell that contains " North" (with a leading space) looks identical to "North" on screen but will not match in a VLOOKUP, FILTER, or merge. Trimming in Power Query removes this problem at the source before the data ever reaches your sheet.

Next up — Data Cleaning in Power Query, where we go deeper into handling nulls, fixing inconsistent values, unpivoting wide tables into tall ones, and the full range of techniques for transforming messy real-world data into analysis-ready output.