Power Query Editor

The Power Query Editor is where every data source gets connected, previewed, and transformed before it loads into the Power BI model. It opens from Home > Transform Data in Power BI Desktop.

Power BI Desktop
       |
       | Home > Transform Data
       |
Power Query Editor

Layout

+------------------+----------------------------+------------------+
|  Queries pane     |  Data preview grid          |  Query Settings  |
|  (left)           |  (center)                   |  (right)         |
|                    |                              |                  |
|  Sales             |  ID | Name | Amount           |  Name: Sales     |
|  Customers         |  1  | ...  | ...              |  Applied Steps:  |
|  Products          |  2  | ...  | ...              |   Source         |
|                    |                              |   Filtered Rows  |
+------------------+----------------------------+------------------+

Three panels do most of the work: the query list on the left, a live preview of the current query in the center, and the step history for the selected query on the right.

Queries Pane

Every data source loaded into the editor appears here as a named query. Selecting a query switches the preview and Applied Steps to that query.

Queries
  |
  +-- Sales
  +-- Customers
  +-- Products
  +-- RawStaging (right-click > Enable Load: off)

Queries can be organized into groups, and a query's Enable Load setting can be turned off for staging queries that other queries reference but that shouldn't themselves load into the model.

Data Preview Grid

Shows the result of the query up to the currently selected step, using a sample of the source data rather than the full dataset, for performance.

Applied Steps: Source -> [Filtered Rows] -> Renamed Columns
                              ^
                      preview reflects up to here

Right-clicking a column header in the preview exposes most common transformations directly — remove, rename, change type, filter, split, and more — without needing the ribbon.

Applied Steps Pane

Every transformation is recorded here, in the order it was applied, as part of Query Settings on the right.

Applied Steps
  Source
  Changed Type
  Filtered Rows
> Renamed Columns   (currently selected)
  Removed Columns

Clicking any step shows the preview as it looked at that point. Steps can be renamed (double-click), reordered (drag), or removed (the "x" that appears on hover) — though reordering can break later steps that depended on the original order.

The Ribbon

The ribbon's tabs group transformations by category:

Home        — common actions: choose columns, reduce rows, combine queries
Transform   — reshape existing columns: split, pivot, group by, data type
Add Column  — create new columns from existing ones
View        — toggle panes, formula bar, and the Advanced Editor

Most transformations are reachable from more than one place — the ribbon, a right-click on a column, or typed directly as M.

Formula Bar

Enabled from View > Formula Bar, this shows the M expression for the currently selected step, and allows editing it directly instead of going through the UI.

= Table.SelectRows(#"Changed Type", each [Status] = "Active")

This is the fastest way to tweak a single step without reopening the full Advanced Editor.

Advanced Editor

Opened from Home > Advanced Editor, this shows the entire query as one M script — every step, in order, in a single let...in expression. See M Language for how to read and write it directly.

What the Applied Steps Pane Produces

Every step built through the UI becomes one line of the Advanced Editor's script. Given this Applied Steps list:

Applied Steps
  Source
  Changed Type
  Filtered Rows
  Renamed Columns
  Removed Columns

Opening Advanced Editor for that same query shows:

let
    Source = Sql.Database("server", "SalesDB"),
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"OrderDate", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Status] = "Active"),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows", {{"Amt", "Amount"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns", {"Notes"})
in
    #"Removed Columns"

Reading it line by line:

• Source connects to the database — this is whatever step created the query, here a Sql.Database call.
• #"Changed Type" corresponds to clicking a column's type icon; it became this name because "Changed Type" contains a space, so M wraps it in #"...".
• #"Filtered Rows" is the row filter applied from a column header's dropdown, referencing the previous step by name.
• #"Renamed Columns" and #"Removed Columns" follow the same pattern: each one wraps the step before it.
• The final in clause names #"Removed Columns" as the query's result — the same step highlighted last in the Applied Steps pane.

Selecting any step in the Applied Steps pane is equivalent to placing the cursor on that same line here; the preview grid always reflects up through whichever step is currently selected, in either view.

Editing this script directly — renaming a step, inserting a line, changing a function's arguments — has the same effect as doing it through the UI, but all at once instead of one click at a time. This is where a query is restructured wholesale, rather than nudged step by step.

Adding Error Handling the UI Doesn't Expose

Some things are only practical to add directly in Advanced Editor, because there's no ribbon button for them. Wrapping a step in try...otherwise is a common example — it lets a query keep running even if one value fails to convert, instead of the whole refresh erroring out.

Starting from a UI-built query:

let
    Source = Csv.Document(File.Contents("orders.csv")),
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"OrderDate", type date}})
in
    #"Changed Type"

If a handful of rows have a malformed date, #"Changed Type" errors on those rows, and the query fails entirely. Editing that line directly in Advanced Editor to add try...otherwise changes the outcome from a hard failure to a handled one:

let
    Source = Csv.Document(File.Contents("orders.csv")),
    #"Changed Type" = Table.TransformColumns(
        Source,
        {{"OrderDate", each try Date.From(_) otherwise null, type date}}
    )
in
    #"Changed Type"

Now a bad date becomes null instead of an error, and the refresh completes with the rest of the data intact. There's no equivalent checkbox for this in the ribbon — it only exists as a direct edit to the M.

See Error Handling in Power Query for the full pattern — including try without otherwise, checking [HasError], and when catching the error is the wrong fix.

What a UI-Built Merge Actually Generates

Merge Queries walks through doing this from the ribbon. Opening Advanced Editor afterward shows what that UI step actually produced:

let
    Source = Sales,
    #"Merged Queries" = Table.NestedJoin(
        Source, {"CustomerID"},
        Customers, {"CustomerID"},
        "Customers", JoinKind.LeftOuter
    ),
    #"Expanded Customers" = Table.ExpandTableColumn(
        #"Merged Queries", "Customers", {"Name"}, {"Customers.Name"}
    )
in
    #"Expanded Customers"

Table.NestedJoin is the merge itself — matching Sales and Customers on CustomerID, using a left outer join — and it produces a column of nested tables. Table.ExpandTableColumn is the separate "expand" step, pulling Name out of each nested table into a real column. Two ribbon actions (Merge, then Expand) become these two M steps, one to one.

Best Practices

• Turn off Enable Load for staging queries that exist only to be referenced by other queries.
• Rename steps to something descriptive instead of leaving the auto-generated names — "Filtered Rows1" tells the next person nothing.
• Use the formula bar for small edits to a single step, and the Advanced Editor when reviewing or restructuring a whole query.
• Check the preview after each step; catching a wrong result early is far cheaper than debugging it five steps later.

Common Mistakes

Working Only in the Preview, Never Checking the Advanced Editor

The preview shows the result, not the logic. Periodically reviewing the Advanced Editor catches inefficient or redundant steps that aren't obvious from the grid alone.

Leaving Staging Queries Loading Into the Model

A query that exists only to be referenced by others, but still has Enable Load on, adds an unnecessary table to the model and slows refresh.

Reordering Steps Without Checking Dependencies

A later step that references a column name or type set by an earlier step can silently break if that earlier step moves.

Editor Checklist

• Queries are named clearly, and staging-only queries have load disabled.
• Applied Steps have descriptive names, not default auto-generated ones.
• The query has been spot-checked in the Advanced Editor, not just the preview grid.
• No leftover debug or test steps remain before publishing.

Next Steps

Continue learning Power Query:

• Transformations
• M Language
• Merge Queries