Data Types

In the M language, data types serve as a framework for classifying and defining data. It’s a hierarchical structure that classifies data, describing what each value looks like.

Primitive Types

Primitive types in M are the building blocks of the type system. They represent the simplest forms of data, such as numbers, text, dates, and booleans. These data types are important because they form the basis from which more complex structures, like records and tables, are constructed. Understanding these primitive types helps in grasping how M handles data at its most fundamental level.

Just like a number or a list is a value, a type is also a value. And with that, it also has its own syntax. For instance:

type text   // A type value classifying text values
type number // A type value classifying number values

To find out what type of value you are working with you can make use of the Value.Type function. When you pass a value to the function, it will return the type of value you are dealing with.

Value.Type( #date( 2024, 1, 1 ) ) // Output: type date
Value.Type( "Rick" )              // Output: type text

M features 18 primitive types, each with distinct characteristics and purposes.

The table contains three columns:

• Primitive Types: This column lists all the primitive types in the M language.
• Type Qualifiers: This section marks the abstract primitive types and specifies which types can serve as custom types.
• Value: This column presents the distinct values defined by each type. It also shows that some types (like any, anynonnull, none) don’t associate with a specific value, and are therefore left empty.

Abstract vs Non-Abstract Types

One of the terms used above is abstract types. These stand opposed to the non-abstract types. Here’s how they are different:

• Non-Abstract Types: A type is considered non-abstract when it classifies a single value. For instance, ‘type number’ is a non-abstract type representing numerical values. Non-abstract types are concrete, meaning they clearly describe a particular kind of data, like numbers, text, or dates.
• Abstract Primitive Types: In contrast, abstract types are more conceptual and do not represent specific, tangible values. Instead, they provide a broader, more generalized classification of data. For example, you can have a record value of type record. However, this does not fully describe the structure of a record. For that, we would need to specify the field names and column types.

So what are some examples of abstract types?

1. Type Function: ‘type function’ is an abstract type representing all function values in M. It provides a general category for any function, regardless of its specific parameters or return type. However, it does not give details about the function’s signature – for that, a custom type would be necessary.
2. Type Record: A record classifies record values. That means all records conform to the ‘type record’. However, type record is not an accurate classification of all records. It does not provide any information on the fields and data types the record contains. For that, we would need a custom type.
3. Type Table: This type categorizes all table values. Similar to type record, type table is a broad classification. It indicates that a value is a table but does not specify details like column names or data types of these columns. For a more detailed classification, a custom table type must be used.
4. Type Any: The most inclusive abstract type, type any classifies every possible value in the M language. It is the ultimate generalist, not bound to any specific data form. However, its broad nature means that it does not classify a single value.
5. Type Anynonnull: This type includes all non-null values. It serves as a way to describe all non-null values. While it’s broader than most non-abstract types, it’s more specific than type any by excluding null values.
6. Type None: An exceptional abstract type, type none is unique as it does not correspond to any possible value. It’s used to represent a state where no value is produced, typically indicating an error or an endless loop. Since it does not classify a single value, it is considered abstract.

The key point is that using Abstract data types to describe a value in the M language is not exact. When you use an Abstract type, it either does not accurately capture the true nature of the value or leads to errors, as seen with ‘type none’.

Nullable Types

Nullable primitive types hold a special place in the M language. These types extend the concept of primitive types to include the possibility of a ‘null’ value, which is important in representing the absence or unknown status of data.

A nullable primitive type is a variation of a basic primitive type that can hold either a specific value (like a number or text) or a ‘null’ value. This dual nature allows nullable types to represent both the primitive type and the absence of data.

Nullable types are important in many operations, especially when interfacing with data sources where the absence of data is a possibility. The two most obvious ones are:

• Handling Missing Data: In data sets, especially those sourced from real-world inputs, missing data is common. When you import data from a database your column may contain a value of type number or it can have nulls. Nullable types allow such columns to be represented accurately in Power Query.
• Flexibility in Functions: Many functions in Power Query are designed to prevent errors. For example, applying a function on a column that can contain null values, could result in an error if the function does not support null values. By using nullable types in your custom functions, you can avoid errors that would otherwise occur with strictly non-nullable types.

In M, you can transform any primitive type into its nullable counterpart by using the ‘nullable’ keyword. This operation broadens the type’s classification to include ‘null’.

To make a type nullable, simply prefix the type with ‘nullable’. For example:

nullable list // Classifies both list and null values
nullable text  // Classifies both text and null values

You can also apply the ‘nullable’ keyword on types that already support null values. In those cases, the types remain the same.

nullable any  // Output: type any
nullable null // Output: type null

There are, however, abstract types that don’t support null values. Applying ‘nullable’ to those types transforms the types into a different one:

nullable none       // Output: type null
nullable anynonnull // Output: type any

Contribute » | Contributors: Rick de Groot