Clean, filter and reshape data in Power Query Editor before loading into Power BI
Power Query is the data transformation layer in Power BI. Every time you click "Transform Data" instead of "Load", you enter Power Query Editor — a separate interface where you clean and reshape data before it enters your report.
Think of Power Query as "preprocessing" — it runs before your data model. Whatever you do in Power Query happens automatically every time the report refreshes. If your source data has messy column names, blank rows, the wrong data type, or columns that need splitting — Power Query fixes it once and applies the fix every refresh.
The best part: every transformation is recorded as a "step" in the Applied Steps panel. You can see exactly what happened, delete steps, reorder them, or insert new ones at any point.
Examples
Most common Power Query transformations
1. REMOVE BLANK ROWS
Home → Remove Rows → Remove Blank Rows
2. RENAME A COLUMN
Double-click the column header → type new name
3. CHANGE DATA TYPE
Click the icon left of column name → choose type
(Text, Whole Number, Decimal, Date, etc.)
4. FILTER ROWS
Click dropdown arrow on column header
Uncheck values to exclude → OK
5. SPLIT COLUMN
Select column → Transform → Split Column
By Delimiter (comma, space, etc.) or By Number of Characters
6. TRIM WHITESPACE
Select column → Transform → Format → Trim
7. REPLACE VALUES
Transform → Replace Values → Old value → New value
8. REMOVE DUPLICATES
Select column(s) → Home → Remove Rows → Remove Duplicates
💡 Every step is logged in the "Applied Steps" panel on the right. Click any step to see the data at that point.
Applied Steps — the magic of Power Query
Applied Steps panel (right side of Power Query Editor):
Source ← connects to the file/database
Navigation ← selects the specific sheet/table
Promoted Headers ← uses Row 1 as column names
Changed Type ← sets data types
Removed Blank Rows ← your cleaning step
Renamed Columns ← your renaming step
...
Each step = one recorded action.
• Click any step to preview data at that point
• Delete a step to undo that transformation
• Right-click → Move Up/Down to reorder
When you click "Close & Apply" — all steps run
in sequence on the fresh data from the source.
💡 This is why Power Query is so powerful — transformations are repeatable and auditable.
Key Points
✓Power Query runs before data loads — every refresh re-applies all your steps automatically
✓Never edit source data manually — let Power Query handle all transformations
✓Use "Transform" tab for column-level changes, "Home" tab for row-level changes
✓Promote Headers (use first row as headers) is almost always the first step needed
✓Close & Apply saves all steps and loads transformed data into Power BI
Common Mistakes
✗ Mistake: Changing data in the source Excel file instead of Power Query
✓ Fix: Always transform in Power Query — source changes can break your steps; PQ changes survive source updates.
✗ Mistake: Loading data with wrong data types (numbers stored as text)
✓ Fix: Always check and set data types in Power Query before closing. Text numbers break SUM() and aggregations.
Practice Question
In Power Query, what does clicking "Close & Apply" do?