Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 56 ยท Power Query

The Power Query Editor

Tour the Power Query Editor: the Queries pane, Applied Steps, the formula bar and the M code behind every click, with a pandas pipeline that mirrors it.

Real data is rarely ready for a report. Column names are odd, dates are stored as text, and three files need to become one table. In Power BI, the place where you fix all of that is the Power Query Editor. You connect to a source (see Connecting to Data Sources), shape the data with clicks, and Power BI remembers every click as a repeatable recipe.

That last part is the big idea. You are not editing the data itself. You are writing a list of instructions that Power BI replays from the original source every time the report refreshes. This lesson shows the editor's parts, how the recorded steps become code in the M language, and how the same pipeline looks in pandas so you can run something live.

Power Query is a graphical tool inside Power BI Desktop (and Excel, dataflows and other products). It cannot run on this site. The M code below is read-only. The runnable examples are Python stand-ins that do the same work, and each section says which is which.

Opening the editor

In Power BI Desktop, choose Transform data on the Home ribbon. The editor opens in its own window. If you choose Get data and then Transform Data in the preview dialog instead, you land in the same place with the new query already selected.

Four areas to know

Queries pane Every query in the report, on the left.
Data preview The first rows of the current step.
Applied Steps The recipe, on the right.
Formula bar The M code of the selected step.
1 / 2

The parts of the window

The Queries pane (left). It lists every query in the file. Each query is one table (or a value, list or function). You can rename, group, duplicate and reorder them. Queries often depend on each other, for example a Sales query that reads a staging query. You will meet that pattern in Custom Functions and Reusable Queries.

The data preview (center). It shows the data as it looks after the selected step. By default it uses only the first rows of a large source, so a preview is quick but not a full scan. The column headers show a small icon for the data type (123 whole number, 1.2 decimal, a calendar for date, ABC for text). The thin bar under each header is a quality bar: green for valid values, red for errors and dark gray for empty values. Turn on View > Column quality and Column distribution to see them in more detail.

Applied Steps (right). This is the recipe. Click any step and the preview shows the data as it was at that point in time. That is a very handy debugging tool: if the result looks wrong, click earlier steps until you find where it went wrong.

The formula bar (top). It shows the M expression of the selected step. If you do not see it, switch it on under View > Formula bar.

How a click becomes a step

Suppose you open a CSV of orders and do four things: use the first row as headers, set types, keep only rows with a positive quantity, and add a revenue column. The Applied Steps list would read:

  1. Source
  2. Promoted Headers
  3. Changed Type
  4. Filtered Rows
  5. Added Custom

Behind the scenes, Power Query wrote this M query. Open it with Home > Advanced Editor:

M
let
    Source = Csv.Document(
        File.Contents("C:\data\orders.csv"),
        [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
    ),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    #"Changed Type" = Table.TransformColumnTypes(
        #"Promoted Headers",
        {{"OrderDate", type date}, {"Quantity", Int64.Type}, {"UnitPrice", type number}}
    ),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Quantity] > 0),
    #"Added Custom" = Table.AddColumn(
        #"Filtered Rows", "Revenue", each [Quantity] * [UnitPrice], type number
    )
in
    #"Added Custom"

A few things to notice, even before you learn M properly in M Language Basics:

  • The query is one let ... in expression. Every line inside let defines a named step. The line after in says which step is the final result.
  • Each step takes the previous step's name as its first argument. That is how steps chain together.
  • Step names with spaces are written #"Like This". If you rename a step to Filtered (without spaces) the #"" wrapper disappears.
  • each [Quantity] > 0 is shorthand for a function that receives the current row, and [Quantity] reads that row's column.
  • M is case-sensitive: Table.SelectRows works, table.selectrows does not.

You rarely have to type M from scratch. Click to build a step, read its formula, and edit the formula only when the dialog does not offer the option you need.

Editing, reordering and deleting steps

Every step has a context menu (right-click) with Rename, Delete, Delete Until End, Insert Step After, Move up/down and Extract Previous (which splits the earlier steps into a separate query). A gear icon beside a step reopens its dialog so you can change its settings.

Two cautions:

  • Order matters. If a later step refers to a column that an earlier step removed, you will get an error such as The column 'X' of the table wasn't found. Move or delete steps carefully; Power Query warns you when an insert would break later steps.
  • Inserting a step in the middle can rewrite the steps that follow, so check the result of the last step afterward.

Good habits: give steps descriptive names ("Remove test orders" beats "Filtered Rows1"), and add a note to a step through Properties when the reason is not obvious.

Closing and applying

When you are done, choose Close & Apply. Power BI runs the queries, loads the results into the data model and returns to the report. The data in the model is a copy created by your steps. If you change the steps later, apply again; if the source changes, Refresh.

Also note Enable load: a right-click option on each query. Queries that are only helpers (staging queries, parameters, functions) should have it switched off so they do not bloat the model.

A pandas pipeline that mirrors the steps

The live part of this lesson is a stand-in. pandas is not Power Query, but a chain of named steps is a good picture of what Power Query does: each step takes the previous result and returns a new table. Below, a list of small functions plays the role of Applied Steps, and the loop shows the preview "as of each step".

Python Applied Steps as a pandas pipeline

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice that Source produced text columns, exactly like a CSV that Power Query has not typed yet. After Changed Type the numbers and dates are real types, and Filtered Rows dropped order 1002. Click-by-click, that is what you see in the Applied Steps pane.

The stand-in differs from the real thing in a few ways: Power Query is lazy (it evaluates steps only as needed and can push work to the database, see Query Folding, Incremental Refresh and Query Performance), and its steps are M expressions rather than Python functions. The shape of the idea is the same.

Replaying on new data

The point of recording steps is that they run again when the source changes. This second example feeds a "new month" of data through the same list of steps unchanged.

Python Replaying the recipe on new data

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In Power BI the equivalent is Refresh: the same steps run against the new file, and a new row count and total appear in your visuals.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the Applied Steps pane represent?

    Applied Steps is the ordered list of transformations. Power Query replays it from the original source on each refresh; the source itself is never changed.
  2. 2. In the M code, what does the line after the keyword in specify?

    A let ... in expression defines named steps inside let, and the expression after in is the value the query returns, normally the last step.
  3. 3. A step name contains spaces, such as Filtered Rows. How is it written in M?

    Identifiers with spaces use the quoted-identifier syntax #"...". Renaming a step to a name without spaces removes the need for it.
  4. 4. You click an earlier step in Applied Steps. What does the preview show?

    Selecting a step previews the table at that point, which makes it easy to find the step that introduced a problem.

Summary

  • The Power Query Editor has a Queries pane, a data preview, Applied Steps and a formula bar.
  • Every click records a step, written in the M language as a named line inside let ... in.
  • Steps chain: each takes the previous step as input, and order matters.
  • Refresh replays the steps against the source; the source is never changed.
  • Turn off Enable load for helper queries, and give steps meaningful names.
  • The pandas pipelines here are stand-ins that show the same chaining idea.

Next, put the editor to work on messy data in Cleaning Data: Types, Nulls, Trim and Duplicates.