This quick tour applies to creating Power Query M formula language queries in Power BI Desktop, Power Query in Excel, and Get Data in Excel 2016.

Note: M is a case-sensitive language.

Create a query with Query Editor

To create an advanced query, you use the Query Editor. A mashup query is composed of variables, expressions, and values encapsulated by a let expression. A variable can contain spaces by using the # identifier with the name in quotes as in #"Variable name".

A let expression follows this structure:

            
let
  Variablename = expression,
  #"Variable name" = expression2
in 
  Variablename
            
          

To create an M query in the Query Editor, you follow this basic process:

• Create a series of query formula steps that start with the let statement.
• Each step is defined by a step variable name which can include spaces by using the # character as #"Step Name".
• A formula step can be a custom formula. Please note that the Power Query Formula Language is case sensitive.
• Each query formula step builds upon a previous step by referring to a step by its variable name.
• Output a query formula step using the in statement. Generally, the last query step is used as the in final data set output.

To learn more about expressions and values, see Expressions and values.

Simple Power Query M formula example

Let's assume you have a table that looks like this:

And, you want to capitalize each word in the Item column to produce the following table:

Here's the code you can paste into the Query Editor's Advanced Editor:

            
let
  Orders = Table.FromRecords({
    [OrderID = 1, CustomerID = 1, Item = "fishing rod", Price = 100.0],
    [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],
    [OrderID = 3, CustomerID = 2, Item = "fishing net", Price = 25.0]
  }),
  #"Capitalized Each Word" = Table.TransformColumns(Orders, {"Item", Text.Proper})
in
  #"Capitalized Each Word"
            
          

Let's review this simple examle and see how it followed the basic process outlined above:

• You started the query using the let statement.
• You defined 2 steps using the variable names Orders and #"Capitalized Each Word".
• In the first step you used the Table.FromRecords function to create a table from a list of supplied records (learn more about these value types in Structured data values).
• In the second step you built upon the previous step by passing its variable name as the first parameter to the Table.TransformColumns function.
• You then output the result of the last step to the final data set by placing its name after the in statement.

A Power Query M formula language query is composed of formula expression steps that create a mashup query. A formula expression can be evaluated (computed), yielding a value.

Let expression

The let expression encapsulates a set of values to be computed, assigned names, and then used in a subsequent expression that follows the in statement. For example, a let expression could contain a Source variable that equals the value of the Text.Proper() formula expression and yields a text value in proper case. This value was assigned the name Source which is then used following the in statement as the final result of the query.

            
let
  Source = Text.Proper("hello world")
in
  Source
            
          

In the example above, Text.Proper("hello world") is evaluated to "Hello World".

The next sections describe value types in the language.

Primitive value

A primitive value is single-part value, such as a number, logical, text, or null. A null value can be used to indicate the absence of any data.

Function value

A Function is a value which, when invoked with arguments, produces a new value. Functions are written by listing the function’s parameters in parentheses, followed by the goes-to symbol =>, followed by the expression defining the function. For example, to create a function called “MyFunction” that has two parameters and performs a calculation on parameter1 and parameter2:

            
let
  MyFunction = (parameter1, parameter2) => (parameter1 + parameter2) / 2
in
  MyFunction
            
          

Calling the MyFunction() returns the result:

            
let
  Source = MyFunction(2, 4)
in
  Source
            
          

This code produces the value of 3.

Structured data values

The M language supports the following structured data values:

• List
• Record
• Table
• Additional structured data examples

Note: Structured data can contain any M value. To see a couple of examples, see Additional structured data examples.

List

A List is a zero-based ordered sequence of values enclosed in curly brace characters { }. The curly brace characters { } are also used to retrieve an item from a List by index position.

Note: Power Query M supports an infinite list size, but if a list is written as a literal, the list has a fixed length. For example, {1, 2, 3} has a fixed length of 3.

The following are some List examples.

Record

A Record is a set of fields. A field is a name/value pair where the name is a text value that is unique within the field’s record. The syntax for record values allows the names to be written without quotes, a form also referred to as identifiers. An identifier can take the following two forms:

1. identifier_name such as OrderID.
2. #"identifier name" such as #"Today's data is: ".

Square brace characters [ ] denote the beginning and end of a record expression, and are used to get a field value from a record.

The following example shows a record and how to get the Item field value.

The example record contains fields named "OrderID", "CustomerID", "Item", and "Price" with values 1, 1, "Fishing rod", and 100.00:

              
let
  Source = [
    OrderID = 1,
    CustomerID = 1,
    Item = "Fishing rod",
    Price = 100.00
  ]
in
  Source
              
            

To get the value of an Item, you use square brackets as Source[Item]:

            
let
  Source = [
    OrderID = 1,
    CustomerID = 1,
    Item = "Fishing rod",
    Price = 100.00
  ]
in
  Source[Item] //equals "Fishing rod"
            
          

Table

A Table is a set of values organized into named columns and rows. The column type can be implicit or explicit. You can use the #table function to create a list of column names and a list of rows. A Table of values is a List in a List. The curly brace characters { } are also used to retrieve a row from a Table by index position (see Example 3 – Get a row from a table by index position).

Example 1 - Create a table with implicit column types

            
let
  Source = #table(
    {"OrderID", "CustomerID", "Item", "Price"},
    {
      {1, 1, "Fishing rod", 100.00},
      {2, 1, "1 lb. worms", 5.00}
    }
  )
in
  Source
            
          

Example 2 – Create a table with explicit column types

            
let
  Source = #table(
    type table [OrderID = number, CustomerID = number, Item = text, Price = number],
    {
      {1, 1, "Fishing rod", 100.00},
      {2, 1, "1 lb. worms", 5.00}
    }
  )
in
  Source
            
          

Both of the examples above creates a table with the following shape:

Example 3 – Get a row from a table by index position

            
let
  Source = #table(
    type table [OrderID = number, CustomerID = number, Item = text, Price = number], 
    {
      {1, 1, "Fishing rod", 100.00},
      {2, 1, "1 lb. worms", 5.00}
    }
  )
in
  Source{1}
            
          

This expression returns the following record:

Additional structured data examples

Structured data can contain any M value. Here are some examples:

Example 1 - List with Primitive values, Function, and Record

            
let
  Source = {
    1,
    "Bob",
    DateTime.ToText(DateTime.LocalNow(), "yyyy-MM-dd"),
    [OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0]
  }
in
  Source            
            
          

Evaluating this expression can be visualized as:

Example 2 - Record containing Primitive values and nested Records

            
let
  Source =
    [CustomerID = 1, Name = "Bob", Phone = "123-4567", Orders =
      {
        [OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0],
        [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0]
      }
    ]
in
  Source
            
          

Evaluating this expression can be visualized as:

Note: Although many values can be written literally as an expression, a value is not an expression. For example, the expression 1 evaluates to the value 1; the expression 1+1 evaluates to the value 2. This distinction is subtle, but important. Expressions are recipes for evaluation; values are the results of evaluation.

If expression

The if expression selects between two expressions based on a logical condition. For example:

          
if 2 > 1 then  
  2 + 2   
else   
  1 + 1 
          
        

The first expression (2 + 2) is selected if the logical expression (2 > 1) is true, and the second expression (1 + 1) is selected if it is false. The selected expression (in this case 2 + 2) is evaluated and becomes the result of the if expression (4).

You can add comments to your code with single-line comments (//) or multi-line comments that begin with /* and end with */.

Example - Single-line comment

            
let
  //Convert to proper case
  Source = Text.Proper("hello world")
in
  Source
            
          

Example - Multi-line comment

            
/* Capitalize each word in the Item column in the Orders table. Text.Proper
is evaluated for each Item in each table row. */
let
  Orders = Table.FromRecords({
    [OrderID = 1, CustomerID = 1, Item = "fishing rod", Price = 100.0],
    [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],
    [OrderID = 3, CustomerID = 2, Item = "fishing net", Price = 25.0]
  }),
  #"Capitalized Each Word" = Table.TransformColumns(Orders, {"Item", Text.Proper})
in
  #"Capitalized Each Word"
            
          

The evaluation model of the Power Query M formula language is modeled after the evaluation model commonly found in spreadsheets, where the order of calculations can be determined based on dependencies between the formulas in the cells.
If you have written formulas in a spreadsheet such as Excel, you may recognize the formulas on the left will result in the values on the right when calculated:

In M, an expression can reference previous expressions by name, and the evaluation process will automatically determine the order in which referenced expressions are calculated.

Let’s use a record to produce an expression which is equivalent to the above spreadsheet example. When initializing the value of a field, you refer to other fields within the record by the name of the field, as follows:

            
[
  A1 = A2 * 2,
  A2 = A3 + 1,
  A3 = 1
]
            
          

The above expression evaluates to the following record:

Records can be contained within, or nested, within other records. You can use the lookup operator ([ ]) to access the fields of a record by name. For example, the following record has a field named Sales containing a record, and a field named Total that accesses the FirstHalf and SecondHalf fields of the Sales record:

            
[
  Sales = [ FirstHalf = 1000, SecondHalf = 1100 ],
  Total = Sales[FirstHalf] + Sales[SecondHalf]
]
            
          

The above expression evaluates to the following record:

You use the positional index operator ({ }) to access an item in a list by its numeric index. The values within a list are referred to using a zero-based index from the beginning of the list. For example, the indexes 0 and 1 are used to reference the first and second items in the list below:

            
[
  Sales =
    {
      [
        Year = 2007,
        FirstHalf = 1000,
        SecondHalf = 1100,
        Total = FirstHalf + SecondHalf // equals 2100
      ],
      [
        Year = 2008,
        FirstHalf = 1200,
        SecondHalf = 1300,
        Total = FirstHalf + SecondHalf // equals 2500
      ]
    },
  #"Total Sales" = Sales{0}[Total] + Sales{1}[Total] // equals 4600
]
            
          

Lazy and eager evaluation

List, Record, and Table member expressions, as well as let expressions (See Expressions and values), are evaluated using lazy evaluation: they are evaluated when needed. All other expressions are evaluated using eager evaluation: they are evaluated immediately, when encountered during the evaluation process. A good way to think about this is to remember that evaluating a list or record expression will return a list or record value that knows how its list items or record fields need to computed, when requested (by lookup or index operators).

The Power Query M formula language includes a set of operators that can be used in an expression. Operators are applied to operands to form symbolic expressions. For example, in the expression 1 + 2 the numbers 1 and 2 are operands and the operator is the addition operator (+).

The meaning of an operator can vary depending on the type of operand values. The language has the following operators:

Plus operator (+)

Combination operator (&)

List of M operators

Common operators which apply to null, logical, number, time, date, datetime, datetimezone, duration, text, binary)

Logical operators (In addition to Common operators)

Number operators (In addition to Common operators)

Text operators (In addition to Common operators)

List, record, table operators

Record lookup operator

List indexer operator

Type compatibility and assertion operators

Date operators

Datetime operators

Datetimezone operators

Duration operators

Note: Not all combinations of values may be supported by an operator. Expressions that, when evaluated, encounter undefined operator conditions evaluate to errors. For more information about errors in M, see Errors.

Error example:

The Power Query M formula language has formulas to convert between types. The following is a summary of conversion formulas in M.

Number

Text

Logical

Date, Time, DateTime, and DateTimeZone

Metadata is information about a value that is associated with a value. Metadata is represented as a record value, called a metadata record. The fields of a metadata record can be used to store the metadata for a value. Every value has a metadata record. If the value of the metadata record has not been specified, then the metadata record is empty (has no fields). Associating a metadata record with a value does not change the value’s behavior in evaluations except for those that explicitly inspect metadata records.

A metadata record value is associated with a value x using the syntax value meta [record]. For example, the following associates a metadata record with Rating and Tags fields with the text value "Mozart":

            
"Mozart" meta [
  Rating = 5,
  Tags = {"Classical"}
]
            
          

A metadata record can be accessed for a value using the Value.Metadata function. In the following example, the expression in the ComposerRating field accesses the metadata record of the value in the Composer field, and then accesses the Rating field of the metadata record.

            
[
  Composer = "Mozart" meta [
    Rating = 5,
    Tags = {"Classical"}
  ],
  ComposerRating = Value.Metadata(Composer)[Rating]   // 5
]
            
          

Metadata records are not preserved when a value is used with an operator or function that constructs a new value. For example, if two text values are concatenated using the & operator, the metadata of the resulting text value is an empty record [].

The standard library functions Value.RemoveMetadata and Value.ReplaceMetadata can be used to remove all metadata from a value and to replace a value’s metadata.

An error in Power Query M formula language is an indication that the process of evaluating an expression could not produce a value. Errors are raised by operators and functions encountering error conditions or by using the error expression. Errors are handled using the try expression. When an error is raised, a value is specified that can be used to indicate why the error occurred.

Try expression

A try expression converts values and errors into a record value that indicates whether the try expression handled an error, or not, and either the proper value or the error record it extracted when handling the error. For example, consider the following expression that raises an error and then handles it right away:

            
try error "negative unit count"
            
          

This expression evaluates to the following nested record value, explaining the [HasError], [Error], and [Message] field lookups in the unit-price example below.

Error record

            
[
  HasError = true,
  Error =
  [
    Reason = "Expression.Error",
    Message = "negative unit count",
    Detail = null
  ]
]
            
          

A common case is to replace errors with default values. The try expression can be used with an optional otherwise clause to achieve just that in a compact form:

            
try error "negative unit count" otherwise 42  // equals 42 
            
          

Error example

            
let Sales =
  [
    ProductName = "Fishing rod",
    Revenue = 2000,
    Units = 1000,
    UnitPrice =
      if Units = 0 then
        error "No Units"
      else
        Revenue / Units
  ],

//Get UnitPrice from Sales record
  textUnitPrice = try Number.ToText(Sales[UnitPrice]),
  Label = "Unit Price: " & (
    if textUnitPrice[HasError] then
      textUnitPrice[Error][Message]
//Continue expression flow
    else
      textUnitPrice[Value]
  )
in
  Label
            
          

The above example accesses the Sales[UnitPrice] field and formats the value producing the result:

            
"Unit Price: 2"
            
          

If the Units field had been zero, then the UnitPrice field would have raised an error which would have been handled by the try. The resulting value would then have been:

            
"Unit Price: No Units"