The Power Query Editor and the M Language

What Is Power Query?

Power Query is the data preparation engine built into Power BI (and Excel). It lets you import, clean and reshape data without writing code, then automatically replay all these operations at every refresh. Your source files are never modified: Power Query reads the source and applies its transformations on the fly.

You open it from Power BI Desktop with Home > Transform data.

The Areas of the Editor

• The Queries pane (on the left): the list of queries; each one becomes a table in the model if loading is enabled.
• The data preview (in the center): a few rows of the selected query. The icon to the left of each header shows the type (ABC for text, 123 for whole number, 1.2 for decimal, calendar for date).
• The Query Settings (on the right): its name and above all the list of applied steps.
• The ribbon: tabs Home, Transform, Add Column, View.
• The formula bar: shows the code of the selected step. If it isn’t visible, turn it on in the View tab.

In the View tab, also turn on Column quality, Column distribution and Column profile: you immediately see the percentage of valid values, errors and blanks. By default, this profile covers the first 1,000 rows; you can extend it to the whole dataset in the status bar.

One Action = One Step

Each click creates a step. For example, removing a column adds the step Removed Columns. Clicking a step shows the data at that stage: an excellent way to debug. You can:

• rename a step (right-click > Rename) to document your work;
• delete a step (cross);
• edit its parameters (gear icon).

Careful: steps are chained. Deleting or editing a step in the middle can break the following ones (for example if they use a column that has disappeared).

The M Language

Behind the interface, every query is a program in the M language. Open Home > Advanced Editor to see it in full:

let
    Source = Csv.Document(File.Contents("C:\Formation\ventes.csv"),
        [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",
        {{"Date", type date}, {"Quantite", Int64.Type}, {"PrixUnitaire", Int64.Type}})
in
    #"Changed Type"

Key points:

• let opens the list of steps, separated by commas;
• each step has a name; names with spaces or accents are written #"...";
• each step generally uses the result of the previous one;
• in indicates the step returned as the result;
• M is case-sensitive: Table.RowCount works, table.rowcount doesn’t.

You don’t need to write M to use Power Query well, but being able to read it will let you understand and fix your queries.

Close & Apply

When your transformations are ready, Close & Apply loads the result into the model. Apply loads without closing the editor.