Expressions, values, and let expression
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. 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 Text.Proper and yields a text value in proper case.
Let expression
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.
| Type | Example value |
|---|---|
| Binary | 00 00 00 02 // number of points (2) |
| Date | 5/23/2015 |
| DateTime | 5/23/2015 12:00:00 AM |
| DateTimeZone | 5/23/2015 12:00:00 AM -08:00 |
| Duration | 15:35:00 |
| Logical | true and false |
| Null | null |
| Number | 0, 1, -1, 1.5, and 2.3e-5 |
| Text | "abc" |
| Time | 12:34:12 PM |
Function value
A Function is a value that, 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 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:
Note
Structured data can contain any M value. To see a couple of examples, go to 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. For more information, go to List values.
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.
| Value | Type |
|---|---|
| {123, true, "A"} | List containing a number, a logical, and text. |
| {1, 2, 3} | List of numbers |
| { {1, 2, 3}, {4, 5, 6} } |
List of List of numbers |
| { [CustomerID = 1, Name = "Bob", Phone = "123-4567"], [CustomerID = 2, Name = "Jim", Phone = "987-6543"] } |
List of Records |
| {123, true, "A"}{0} | Get the value of the first item in a List. This expression returns the value 123. |
| { {1, 2, 3}, {4, 5, 6} }{0}{1} |
Get the value of the second item from the first List element. This expression returns the value 2. |
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:
identifier_name such as OrderID.
#"identifier name" such as #"Today's data is: ".
The following is a record containing fields named "OrderID", "CustomerID", "Item", and "Price" with values 1, 1, "Fishing rod", and 100.00. 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 examples show a record and how to get the Item field value.
Here's an example record:
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 #table to create a list of column names and 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 (go to 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 previous examples creates a table with the following shape:
| OrderID | CustomerID | Item | Price |
|---|---|---|---|
| 1 | 1 | Fishing rod | 100.00 |
| 2 | 1 | 1 lb. worms | 5.00 |
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 follow record:
| Field | Value |
|---|---|
| OrderID | 2 |
| CustomerID | 1 |
| Item | 1 lb. worms |
| Price | 5 |
Additional structured data examples
Structured data can contain any M value. Here are some examples:
Example 1 - List with [Primitive](#_Primitive_value_1) values, [Function](#_Function_value), and [Record](#_Record_value)
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 isn't 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).