Power BI Basics›Core· 35 min read

Clean & Shape Data in Power Query

Messy data in, tidy data out — Power Query is the kitchen where you wash and chop your data before serving it.

What you will learn

Open the Power Query Editor
Fix data types and remove bad rows
Understand recorded Applied Steps

Why clean data first?

Real data is rarely tidy. It has blank rows, wrong types (numbers stored as text), extra columns, and inconsistent spelling like north vs North. Power Query is the built-in tool for cleaning all of this — before the data ever reaches your charts.

Think of it like a kitchen: Power Query is where you wash and chop the ingredients so the meal (your dashboard) comes out clean.

Opening Power Query

When you click Transform Data (in the Get Data preview, or on the Home ribbon), the Power Query Editor opens in a new window. It looks like a spreadsheet, but every change you make is recorded as a step.

Common cleaning actions

Problem	What you click
Numbers stored as text	Click the column type icon → choose Whole Number
A useless column	Right-click the column header → Remove
Blank or junk rows	Home → Remove Rows → Remove Blank Rows
Messy text like “ north ”	Transform → Format → Trim (cuts spaces)
A column you want to rename	Double-click the header and type a new name

A worked clean-up

Say the imported Amount column came in as text (notice the ABC icon on the header instead of 123). You click the icon and choose Whole Number. Power Query records this in the Applied Steps list on the right:

The Applied Steps panel records every change in order

Applied Steps
  Source
  Promoted Headers
  Changed Type          <- you just added this one
  Removed Blank Rows

Note: Output: The Amount column header now shows the 123 number icon instead of ABC, which means Power BI will treat it as a real number you can add up. Each line in Applied Steps can be clicked to see the table at that point, or deleted with the X to undo just that step.

Steps replay automatically

The magic of Power Query: those steps are saved and replayed. Next month when the file has new rows, you click Refresh and Power BI re-runs every cleaning step on the fresh data — no manual fixing again.

When you are happy, click Close & Apply (top-left) to send the cleaned table back into your report.

Tip: Made a mistake? You do not undo by hand — just click the X next to the wrong step in Applied Steps. The data jumps back as if that step never happened.

Watch out: Always fix data types here in Power Query. If a number column is left as text, you will not be able to sum or average it later, and your totals will silently break.

Q. What does the Applied Steps panel in Power Query do?

Answer: Applied Steps lists each transformation you made. Power BI replays them whenever the data refreshes, so cleaning is done once and reused forever.

✍️ Practice

In Power Query, change a text column to Whole Number and watch the header icon change.
Rename a column by double-clicking its header, then delete that step from Applied Steps.

🏠 Homework

Import a messy file, then use Power Query to fix at least two things (a type and a column name). List the steps that appear in Applied Steps.