Basics of M Language

Power Query is the data transformation engine, and M is the functional, case-sensitive language that powers it. While the UI creates steps  automatically,  understanding the underlying code allows for complex data transformations.

Anatomy of a Query

Every M query is encapsulated in a let ... in block that defines the scope of variables and the final output.

Example Breakdown

let
    Source = Table.FromRecords({[Name = "A", Pay = 100], [Name = "B", Pay = 200]}),
    #"Tax Rate" = 0.2, // 20% is current rate
    Tax = List.Sum(Source[Pay]) * #"Tax Rate"
in
    Tax

• let: Initializes the expression block.
• Commas: Used to separate variables. Every line end with a comma except for the final variable before the in statement.
•  // is used for single-line comments; /* ...*/ for multi-line blocks. Comments are ignored by Power Query and exist solely for readibility of the code.
•  Source: Represents the data origin. While this example builds a table in memory, in practice this usually points to an external data source such as a file or a database.
• #"Tax Rate": A named variable. In M, if a variable name contains spaces, special characters, or starts with a number, it must be wrapped in Quoted Identifier syntax: #" ".
• List.Sum: A standard library function. Using Source[Pay] extracts the "Pay" column as a list, which the function then aggregates.
•  in: Specifies the return value, irrespective of number of steps, this defines the end result of the query. Note that in can return any variable defined in the let block, not just the very last one.

Value Types in M

Primitive Values

Single instance data including Number, Text (always in double quotes), Logical (true/false), Time, DateTime, and Null.

Structured Data

MyTable = #table(
    {"Order ID", "Vendor", "Date", "Amount"},
    {
        {"100", "AA", #date(2020,12,31), 5000},
        {"200", "BB", #date(2021,01,01), 10000}
    }
)