Null Values

A null value is used to represent the absence of a value, or a value of indeterminate or unknown state (i.e., in the case of a missing value).

Structure

A null value is written using the literal null.It is critical to note that null is different from 0. The former is the absence of a value, while 0 is a specific numerical value, just like 1 or 732.

Examples

if [Value] = null then “NA” else [Value]

Related Functions

Null has no direct related functions in M, but the related functions for many other values take null values explicitly into account.

Special Considerations

• Equality Comparisons – Null has some unique properties with regard to how expressions involving the quality operator are evaluated. For example, null is only strictly equal to itself.

null = null    returns true
null = true     returns false
null = false    returns false
null <= null     returns null
null >= null     returns null
null <> null  returns null
null <...

Number Values

Number values in M are used to express numeric amounts and for mathematical calculations. While the concept of “a number” is familiar to everyone, number values in M have the most varied literal forms of any value kind.

Structure and Examples

All of the expressions below represent valid number values in M:

Figure 3.6: Number Values in M

Related Functions

Due to the wide range of mathematical operations that can be performed on number values, there are 52 different related number functions in M, spread across the following six categories:

• Bitwise– These functions directly manipulate the bits of a number. However, because M is a functional language and typically does not involve low-level binary transformations, bitwise operations are not well documented nor commonly used in M.
• Creation and Conversion – These functions are used to create number values or convert them to or from compatible kinds of values.
• Informational – The functions...

Text Values

In M, text values are sequences of characters, which are used to represent text data.

Structure

Text values in M are indicated by “ “

Examples

“Basketball”
“23”
“November 9, 1993”

Related Functions

Because all non-abstract, non-structured value kinds except for null can be converted to and from text values, it’s not surprising that there are 45 text-related functions, spread over the following categories:

• Conversion and Creation – Functions that create text characters and values, and to convert them to other kinds of values
• Extraction and Position – Functions that determine the position of characters within text values, and to extract different subsets from within text values
• Formatting – Functions that clean unwanted characters, and to control text case and other formatting characteristics
• Information – Functions that determine length and contents of text values
• Transformation...

List Values

A list is a series of values. In M, a single column of values is a list, and lists can contain any type of primitive or structured values.

Structure

Lists are initiated by curly brackets “{ }”

Examples

{ 1, 2, 3 }
{ “Cat”, “Dog”, “Monkey” }
{ { 1, 2, 3 }, { “Cat”, “Dog”, “Monkey” } }

Related Functions

Lists are one of the most flexible and powerful value kinds in M, so it is not surprising that there are a vast array of related list functions. These 71 related functions fall into five categories:

• Generation – functions that create lists with different specified characteristics, e.g., lists of date, numbers, random numbers, as well as more complex functions that let you create lists recursively
• Informational – primarily functions that output a logical value depending on the contents of a given list
• Selection – functions that select an item or group...

Record Values

A record is a named list of values. You also can think of a record as a single row in a table, where each field in the row has a column name and a value. Like lists, records can contain either primitive or structured values.

Structure

Records are initiated by square brackets “[ ]”

Examples

[Planet = “Earth”,  MilesFromSun = 92960000, AdjacentPlanets = { “Venus”, “Mars” }]

Figure 3.7: Creation of a record in M, also showing the values within a nested list

Related Functions

The 23 functions related to records are spread across five categories that largely parallel the categorization of list functions. However, there no Statistical records functions, and one new category added:

• Conversion – functions that create a record from a list or table, or a table or list from a record
• Geospatial – functions that handle geospatial data in point or well-known text (WKT) form
• Informational - functions that output...

Table Values

A table is a structured value composed of: 1) Rows, where each row represents a record; 2) Columns, where each column represents a field of a record and has a specific datatype; and 3) Headers, which are the names of each column.Tables can be created in many different ways in M, including using the #table constructor, or from values, lists, records, columns or rows using the appropriate M function.

Structure

table(
columns as any,
rows as any,
) as any

Examples

let
    Source = #table(
        type table [ProductID = text, ProductQuantity = number, Column3 = date],
        {
            {“P001”, 10, #date(2023, 8, 13)},
            {“P002”, 25, #date(2023, 8, 14)},
            {“P003”, 30, #date(2023, 8, 15)}
        }
    )
in
    Source

There are many ways to construct tables using #table, as well as other functions that creates tables from other kinds of primitive and structured value. These methods will be addressed in...

Function Values

Function values are those that when invoked with a set of input values (i.e., arguments) produce a new value.

Structure

In M, functions are specified by listing the input parameters of the function in parentheses, then the “goes to” operator =>, then the expression that defines the function.

Examples

Concatenator = (parameter1, parameter2) => (parameter1 & parameter2)
DifferentDistinct = (x) => List.Difference(x,List.Distinct(x))

Related Functions

Given their inherent power and flexibility, function values have only five related functions – two pertaining to function creation, two pertaining to invoking custom functions, and one informational function to determine if a given function is a data source.

Special Considerations

• Flexibility and Power – Functions can be assigned to variables, passed as arguments, and returned from other functions, making them some of the most versatile and powerful kinds of values in M.
• Importance...