Power Query (Data Transformation Layer) is the heart of Power BI for data preparation. It is Microsoft’s powerful, low-code/no-code ETL (Extract, Transform, Load) engine that lets you connect to hundreds of data sources, clean, reshape, and transform messy data into a clean, analysis-ready model before it reaches the data model (Power BI’s storage engine).

Power Query works using a step-by-step recording system, so every change you make is transparent, reproducible, and can be refreshed automatically when source data changes.

Macro: Data Cleaning & Transformation

1. Power Query Editor

This is the dedicated interface for data transformation.

How to open it:

• In Power BI Desktop → Home tab → Transform data (or right-click a query in the Navigator and choose Transform Data).

Key Panes in Power Query Editor:

• Queries pane (left): List of all queries (tables).
• Data preview (center): Spreadsheet-like view of your data.
• Formula bar (top): Shows the M code for the selected step.
• Applied Steps (right): Complete audit trail of every transformation.
• Query Settings pane (right, below Applied Steps).

2. Applied Steps

This is the most important concept in Power Query.

• Every action you perform creates a new step (e.g., “Changed Type”, “Removed Columns”, “Filtered Rows”).
• Steps are executed top to bottom.
• You can click any step to see how the data looked at that point.
• You can delete, rename, or reorder steps (with some limitations).