Introduction to Power Query

Power Query is the engine that sits between your raw data sources and your Power BI data model. Every time you clean a column, remove duplicates, or change a data type in Power BI Desktop, you are writing Power Query steps — whether you realise it or not. This lesson explains what Power Query actually is, how it fits into the Power BI workflow, and why understanding it deeply makes every other part of Power BI easier.

What Power Query Does

Raw data from the real world is almost never ready to use. Columns have the wrong names, dates are stored as text, there are blank rows, duplicate entries, inconsistent capitalisation, and values split across columns that should be merged. Power Query is the tool that fixes all of this before the data ever reaches your model or your visuals.

The critical thing to understand is that Power Query never changes your source file. The original Excel file or database table is untouched. Power Query reads the source, applies your transformation steps in memory, and loads the result into the model. If the source file changes tomorrow, Power Query re-reads it and re-applies all your steps automatically on the next refresh.

Power Query vs Other Tools

Opening the Power Query Editor

You access Power Query through Power BI Desktop — it is not a separate application. There are two ways to open it, and both take you to the same editor window.

The Power Query Editor — Full Layout

The Four Zones of the Editor

What Are Applied Steps?

Every action you take in the Power Query Editor is recorded as a step in the Applied Steps list. Steps run from top to bottom every time the query is refreshed. This is the fundamental concept that makes Power Query so powerful — your entire cleaning process is saved, repeatable, and editable.

Introduction to M Code

Every Applied Step is stored as a line of M code — the language Power Query runs under the hood. You do not need to write M code to use Power Query effectively, but understanding its basic structure lets you read what your steps are doing, fix errors, and handle transformations that the UI cannot do alone.

Basic M code structure — every query is a let...in expression:

let
    // Step 1 — connect to source
    Source = Excel.Workbook(File.Contents("C:\Data\Sales.xlsx"), null, true),

    // Step 2 — navigate to the Orders sheet
    Orders_Sheet = Source{[Item="Orders", Kind="Sheet"]}[Data],

    // Step 3 — promote first row to headers
    #"Promoted Headers" = Table.PromoteHeaders(Orders_Sheet, [PromoteAllScalars=true]),

    // Step 4 — set column data types
    #"Changed Type" = Table.TransformColumnTypes(
        #"Promoted Headers",
        {
            {"OrderID",   Int64.Type},
            {"OrderDate", type date},
            {"Revenue",   type number},
            {"Status",    type text}
        }
    ),

    // Step 5 — remove blank rows
    #"Removed Blank Rows" = Table.SelectRows(
        #"Changed Type",
        each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
    )

in
    // The last step name is what gets loaded into the model
    #"Removed Blank Rows"

Close and Apply vs Apply vs Discard

Changes you make in the Power Query Editor are not immediately applied to the model. You control when they take effect using three options in the Home ribbon of the editor.

Practice

Lesson Quiz

Next up — Lesson 17 goes deep into the Query Editor Layout — every ribbon tab, every right-click menu, and the column header controls that most users never discover.