Power Query Basics

Analogy categoryAnalogies

What Is Power Query?

Power Query is the data connection and transformation engine built into Power BI, Excel, and other Microsoft tools. Instead of manually copying and reshaping data every time it changes, you build a query once — a recorded sequence of steps written in the M language — and Power BI replays that sequence automatically on every refresh, giving you a repeatable, auditable ETL (extract, transform, load) pipeline.

🏏

Cricket analogy: Just as a bowling coach records a bowler's run-up and release into a fixed drill that gets repeated in every net session, Power Query records your cleanup steps once so they replay identically on every new ball of data.

The Power Query Editor Interface

The Power Query Editor shows every query in a left-hand pane, a data preview grid in the center, and an Applied Steps list on the right that records each transformation you perform — filtering rows, renaming columns, changing types — in the exact order you performed it. You can click any step to see the data as it looked at that point, insert a new step in the middle, or delete a step entirely, making the whole pipeline visual and editable.

🏏

Cricket analogy: The Applied Steps list is like a ball-by-ball commentary log of an innings: you can scroll back to over 14 and see exactly what the scoreboard looked like before the next delivery changed it.

Data Sources and Connectors

Power Query ships with dozens of connectors — Excel workbooks, SQL Server, folders of CSV files, SharePoint lists, REST APIs via the Web connector, and more — each exposed through Get Data. When you connect to a source, Power Query first shows a Navigator pane so you can pick specific tables or files before loading, and for supported sources like SQL Server it can push filtering and aggregation back to the source engine, a behavior called query folding.

🏏

Cricket analogy: Query folding is like a scorer at the ground doing the run-rate calculation before radioing the number to the studio, instead of the studio recalculating from raw ball-by-ball data every time.

powerquery-m

let
    Source = Sql.Database("salesdb.company.com", "Sales"),
    Orders = Source{[Schema="dbo", Item="Orders"]}[Data],
    FilteredRows = Table.SelectRows(Orders, each [OrderDate] >= #date(2026, 1, 1)),
    RenamedColumns = Table.RenameColumns(FilteredRows, {{"CustID", "CustomerID"}}),
    ChangedType = Table.TransformColumnTypes(RenamedColumns, {{"OrderDate", type date}, {"Amount", type number}})
in
    ChangedType

When connecting to relational sources like SQL Server, keep your early steps limited to operations that fold — filtering, sorting, renaming, basic type changes — because query folding lets Power Query push that work back to the source database, which is dramatically faster than pulling millions of rows into memory first.

Applied Steps and the M Formula Language

Every action you perform in the Power Query Editor UI — removing a column, splitting a column by delimiter, promoting headers — is translated into a line of M code, the functional language underlying Power Query, visible in the formula bar. Because each step is just a variable assignment referencing the previous step's output, you can open the Advanced Editor to read, edit, or hand-write the entire query as M code directly, which is essential once transformations get more complex than the UI can express.

🏏

Cricket analogy: This is like DRS technology translating an umpire's on-field call into a precise ball-tracking trajectory that can be reviewed frame by frame, giving you the underlying data behind the visible decision.

Renaming or removing a column early in your steps can silently break a later step that references the old column name, because each step is hard-coded to reference specific column names from the previous step. Always check the Applied Steps list for red error triangles after making changes near the top of a query.

Power Query is Power BI's built-in ETL engine, recording transformations as a reusable, replayable sequence of steps.
Every UI action generates a line of M code, visible and editable in the formula bar or Advanced Editor.
The Applied Steps pane lets you click back to any point in the pipeline to inspect intermediate data states.
Get Data exposes dozens of connectors, each routed through a Navigator pane before data loads into the editor.
Query folding pushes supported operations back to the source database for far better performance on large datasets.
Keep foldable operations (filter, sort, rename, type changes) early in the step sequence to preserve folding.
Renaming or deleting columns early can break downstream steps that reference the original column names.