Power Query M Primer (Part 16): Type System I – Basics

Power Query’s type system helps us understand the values we works with, allows us to describe the kinds of data expected by functions we write, offers a means to share documentation (which IntelliSense can display) and provides a mechanism to inform consumers about the structure of the data we’re handing them (which the host environment can use—for example, guiding Power BI to correctly set column types).

To sum up M’s approach to types at a high level:

Every value has a type. A type is itself a value. Types are used to classify values.

A simple statement, but with a lot packed into it and behind it—so much, in fact, that we’ll explore Power Query’s type system in over several posts. Today, we’ll start with the basics, centered around what are known as nullable primitive types. Later, as we get deeper in, hold on to your hat—you might find a major puzzling surprise, where the type system doesn’t work the way you’d expect.

Let’s start delving into the type system by examining the summary statement we read a moment ago.

Series Index

• Introduction, Simple Expressions & let (part 1)
• Functions: Defining (part 2)
• Functions: Function Values, Passing, Returning, Defining Inline, Recursion (part 3)
• Variables & Identifiers (part 4)
• Paradigm (part 5)
• Types—Intro & Text (Strings) (part 6)
• Types—Numbers (part 7)
• Types—The Temporal Family (part 8)
• Types—Logical, Null, Binary (part 9)
• Types—List, Record (part 10)
• Tables—Syntax (part 11)
• Tables—Table Think I (part 12)
• Tables—Table Think II (part 13)
• Control Structure (part 14)
• Error Handling (part 15)
• Type System I – Basics (part 16) [this post]
• Type System II – Facets (part 17)
• Type System III – Custom Types (part 18)
• Type System IV – Ascription, Conformance and Equality’s “Strange” Behaviors (part 19)
• Metadata (part 20)
• Identifier Scope & Sections (part 21)
• Identifier Scope II – Controlling the Global Environment, Closures (part 22)
• Query Folding I (part 23)
• Query Folding II (part 24)
• Extending the Global Environment (part 25)
• More to come!

Type System Basics

Every value has a type.  

1 // evaluates to a value which is of type number
"Hello World!" // evaluates to value which is of type text
true // evaluates to a logical value (that is, of type logical)
null // evaluates to a null value

Text.From(123) // produces a value of "123", which is of type text
Number.From("123") // produces a value of 123, which is of type number

The type of a value can be obtained by using Value.Type:

Value.Type(1) // outputs "number"

let
  Data = #date(2020, 1, 31),
  Type = Value.Type(Data)
in
  Type // outputs "date"

A type is itself a value.

What’s returned by Value.Type is itself a value—a type value. A type value is just like any other value: it can be saved to a variable, processed by functions and have appropriate operators applied to it. The type of a type value is type type (how’s that for a mouthful!).

Value.Type("abc") // returns a text type value
Value.Type(Value.Type("abc")) // returns a type type value

A number of library functions return type values. These values can also be created using language syntax. While we’ll learn more about this syntax later, as a start, here are several examples:

type number // evaluates to a value of type type (a type value) that holds type number
type null // produces a type value holding type null

let
  NumberType = type number
in
  NumberType // produces a type value holding type number

If a type value is rendered in the user interface, a textual summary of the type is displayed (e.g. the string “type number”). This output is just a textual summary. Type values are not strings, nor is the summary string guaranteed to include all of the type’s details. It’s necessary to use library functions to access the full set of information in a type value (we’ll encounter several of these functions later).

Types are used to classify values.

Classifying can mean describing—for example, using a table’s type to learn about the columns it contains. Classifying can also involve grouping into categories—like how, in a sense, the mashup engine categorizes a value that’s passed as a function parameter as either acceptable or unacceptable based on whether the value’s type aligns with the parameter’s type expectation. If it does—if the value’s type is acceptable—the value is allowed to be passed in; if not—if the value’s type is unacceptable—an error is raised.

The function signature below includes type assertions on both its argument and its return value. The mashup engine will enforce these expectations, requiring that values passed in and returned comply with the respective assertion.

(ages as list) as text => "Combined years of life: " & Text.From(List.Sum(ages))

At first glance, the as number and as text assertions might seem to suggest that the parameter value must be of type list and return value must be of type text. Not quite. Instead, these assertions mandate that those values must be compatible with the specified types (in this case, the parameter must be compatible with type list and the return value compatible with type text).

There’s a difference between a value being of a certain type and being compatible with a certain type. These compatibility checks are performed at the nullable primitive type level. To thoroughly understand these checks, we need a solid grasp on this family of types.

Nullable Primitive Types

So far in this series, we’ve talked about the following types:

binary, date, datetime, datetimezone, duration, function, list, logical, null, number, record, table, text, time, type

All of these are nullable primitive types, but they don’t make up the full set of nullable primitive types. Let’s meet the rest of the family!

Type any

All types are compatible with type any. All values are compatible with type any.

Jump back to function signatures for a moment, if you define a parameter without specifying a type assertion, that assertion defaults to “any”. Similarly, if you omit specifying a return assertion, it defaults to “any”.

The following two function signatures are equivalent in behavior:

(somevalue) => ...
(somevalue as any) as any => ...

Type any is an abstract type. No value can ever directly be of an abstract type. You can have a type value that holds an abstract type (e.g. TypeValue = type any) but you’ll never see a value whose contents are of an abstract type (e.g. no expression can produce a value whose type is any). Even though a value can’t be of an abstract type, abstract types are still useful because they provide a way to classify values.

Type any has its place—like a good default, indicating that values of all types are allowed—but it’s not super exciting.

Nullable and Null-allowing Types

What if you’re building a function which should process values of a certain type and nulls?

Say you’re processing numbers from a database column that allows nulls. A function signature like (amount as number) => ... works great for numbers from the column, but won’t be compatible with the nulls it may contain. You could fall back to (amount as any) => ..., which allows both numbers and nulls–but also unfortunately allows any other kind of value, which is a bit much.

nullable to the rescue! Take a type, proceed it with nullable and a type compatible with the given type as well as with null will be returned (with one exception, which we’ll come to).

type nullable text // effectively equivalent to "type text or type null"

let
  TypeValue = type number
in
  type nullable TypeValue // outputs type nullable number

For example, used in a function argument assertion:

(amount as nullable number) => …// the parameter accepts numbers and nulls

(Note: In M, function signatures must be hard-coded, so using nullable with a variable isn’t allowed in function signatures. Late, we’ll learn a partial way around this.)

Normally, the type returned by nullable is the abstract type nullable T, where T is the type you started with.

However, if you apply nullable to a type that is already compatible with null, the type you started with is simply returned—because that type already allows nulls. Using nullable doesn’t somehow make that type to doubly allow nulls.

type nullable null // returns type null (because it is already compatible with null), not type nullable null
type nullable any // returns type any (because it is already compatible with null), not type nullable any

To go the opposite direction—take a nullable type and retrieve its non-nullable counterpart, use Type.NonNullable:

Type.NonNullable(type nullable text) // type text

If the specified type already doesn’t allow nulls, the method simply returns the type that was passed into it. It doesn’t somehow make the type to doubly disallow nulls.

Type.NonNullable(type text) // type text

This idea of going from a nullable type its non-nullable counterpart brings us to two special type pairings. You’ve previously met the nullable type from each pairing. It’s now time to meet their non-null-allowing counterparts.

Type anynonnull

Type any is compatible with all types, including type null. What is its non-nullable counterpart? anynonnull. This abstract type works just like its name implies: it is compatible with all types, except for those that are compatible with null; any value, except for null, is compatible with this type.

Type.NonNullable(type any) // type anynonnull
	
// going the other direction:
type nullable anynonnull // type any

For example, when used on a function parameter, any value except for nulls will be allowed.

(input as anynonnull) => ... // a function whose argument accepts any value except for null

Type none

What is type null’s non-nullable counterpart? Introducing the strange case of abstract type none! No value can ever be of, or compatible with, type none.

Type.NonNullable(type null) // type none
	
// going the other direction:
type nullable none // type null

The type null-type none pairing is the sole exception to nullable rule stated previously, which said that that applying nullable to a type returns a type that is compatible with the given type and null. nullable none returns type null which is not compatible with type none. Type none is compatible only with itself, type any (because all types are compatible with type any) and type anynonnull (because all non-null-allowing types are compatible with anynonnull).

If types are used to describe and classify values and no value can ever have type none, what’s the point of none? Nothing?

Remember that every M expression must either return a value or raise an error. Well, an expression that always raises an error can be described as “returning” type none because it never actually returns anything.

Why would you have an expression that always errors? How about a helper function that raises error messages?

(problem as text) as none => error Error.Record("Business Rule Violation", problem)

The above as none return assertion will be enforced by the mashup engine. If the function tries to return a value (say someone modifies it down the road to do this but leaves the as none in place), the mashup engine will error because the value being returned is not compatible with type none.

There’s one other case where an expression can be thought of as “returning” type none. What if an expression takes a long time to return a value…a very long time…like an infinitely long time? Technically, an expression that never gets around to returning a value can also be described as “returning” type none. (Why someone would write a pointless expression like that is a different question…but it is theoretically possible to do.)

The Internal, Secret Type action

Lastly, there is a special, secret (a.k.a. Microsoft internal) type named action, which is not mentioned anywhere in the official language specification and which isn’t practically usable from consumer-facing Power Query tooling. As such, it’s beyond the scope of this Primer, so its details won’t be described here.

If you’re interested in learning about this type, you may enjoy reading M Mysteries: The Mysterious Type Action—An M-Internal Means to Write Data Modifications to External Systems.

The Entire Family

Put all the types together that we just discussed and you have the complete family of Power Query’s nullable primitive types:

action, any, anynonnull, binary, date, datetime, datetimezone, duration, function, list, logical, none, null, number, record, table, text, time, type, plus the nullable T variant of each (except for those that don’t have a nullable T variant because they already are compatible with null or are paired)

Out of the this family, any, anynonnull, none and the nullable T types are abstract, as well as types table, record and function.

The latter three being abstract might be surprising. All tables are compatible with type table, all records with type record and all functions with type function—but types table, record and function by themselves do not full describe a particular table, record or function. To do that requires a custom type: a derived type that contains, for example, details on the particular table’s columns, the particular record’s fields or the particular function’s parameters. (Custom types sometimes also come into play with lists, even though type list isn’t abstract.) We’ll talk in depth about custom types later on.

Now that you have a foundation spanning the full nullable primitive type family, we’re ready to discuss compatibility checking.

Compatibility Checking

If you’d like to determine whether a value is compatible with a type, you might try the type compatibility operator—keyword is. This operator returns true or false, depending on whether the given value is compatible with the specified nullable primitive type.

1 is number // true
1 is nullable number // true -- because the value is either of type number or type null
1 is anynonnull // true -- because the value is not null
1 is any // true -- "is any" returns true for any value
	
1 is none // false -- returns false, regardless of the value, because no value is compatible with type none
1 is text // false
1 is null // false

#table({"Col1"}, {}) is table // true
#table({"Col1"}, {}) is list // false
#table({"Col1"}, {}) is date // false

SomeValue is date // answer depends on what SomeValue holds
SomeValue is any // true -- because SomeValue has to hold some kind of value and all values are compatible with any
SomeValue is anynonnull // answer depends on whether SomeValue holds a null
SomeValue is none // false -- because variables always hold values and no value is compatible with type none

Suppose you find yourself frequently using the same set of special formatting rules to render values of various types to text. A custom Text.From-like function that takes any value and returns a string formatted using the appropriate rule, selected based on the value’s type, might be most helpful. The type compatibility operator can be used to pull this off:

(input) => 
if input is date then Date.ToText(input, "MMMM d")
else if input is time then Time.ToText(input, "\h:h \m:m \s:s")
else Text.From(input)

The  type assertion operator—keyword as—checks whether a value is compatible with a given nullable primitive type, returning the value if yes and raising an error if no.

1 as number // returns 1
1 as any // returns 1
1 as text // raises "Expression.Error: We cannot convert the value 1 to type Text."

Type assertions in function signatures are also defined using as syntax, though technically that as is different from the type assertion operator’s as. However, in effect, the two work the same way: A value is tested for compatibility with the given nullable primitive type. If compatible, the value is returned (in case of the type compatibility operator) or allowed through (in case of a function signature assertion); if not, an error is raised.

Both the type compatibility operator and the type assertion operator require that the type’s name be hard-coded. If you’d rather pass the type in via a variable or expression, Value.Is and Value.As are available for your comparison pleasure.

let
  TestType = type number
in
  Value.Is(1, TestType) // true
  //Value.Is("abc", TestType) // false

let
  TestType = type number
in
  Value.As(1, TestType) // returns 1
  //Value.As("abc", TestType) // raises "Expression.Error: We cannot convert the value "abc" to type Number."

Important: For both Value.As and Value.Is, the type value used as the second argument must be a nullable primitive type in order for these methods to work as expected.

Both is/Value.Is and as/Value.As test compatibility of a value with a type. It is also possible to test two types for compatibility using Type.Is. This function checks whether the type given as its first argument is compatible with its second argument (which must be a nullable primitive type in order for the function to work correctly).

Type.Is(type number, type any) // true - number is compatible with any
Type.Is(type any, type number) // false -- any is not compatible with number
	
Type.Is(type logical, type nullable logical) // true - logical is compatible with either logical or null, which is what type nullable logical effectively means
Type.Is(type null, type nullable logical) // true
	
Type.Is(type none, type any) // true -- proving that none is compatible with type any even though there can never be a value of type none
Type.Is(type none, type anynonnull) // true -- proving that none is compatible with type anynonnull

Dynamically Typed

Power Query is dynamically typed. All checks on whether a value is compatible with a given type are done by looking at the value’s type at runtime. There’s no concept of a variable having a type nor is type casting relevant—the actual value’s type is what matters.

Below, Inner‘s as any has no effect on the type of the value that’s stored in Result. The as any does not change the value’s type to any. It simply ensures that whatever is returned from Inner is compatible with any. The type of the value in Result will be the type of whatever was returned by Inner (text, number, null, etc.). That type could change each time the expression in invoked, depending on what Inner returns.

let
 Inner = () as any => ...,
 Result = Inner()
…

Variable Result itself does not have a type. It’s not of type any, or anything like that. It simply is a variable that holds a value—and that value is what is of a type.

To emphasize the fact that the value’s type is what matters, consider the following:

let
 Inner = () as any => ...,
 Outer = (input as anynonnull) => ...
in
  Outer (Inner())

As before, Inner has an as any assertion. Outer expects a value that is as anynonnull. Type any is incompatible with anynonnull—but this expression does not cause the two types used in the assertions to be checked for compatibility. Inner‘s as any simply ensures that that method returns something compatible with any; Outer‘s as anynonnull simply ensures that the value passed in is compatible with “anynonnull.” It is possible to have a value that is compatible with both assertions even though the assertions’ types themselves are incompatible.

At runtime, if the value returned by Inner() is non-null, this expression will work fine; if it is not, an error will be raised. This may be a bit different than what you’re used to if you come from a statically typed background.

To recap: The value’s type is what matters. Type assertions simply verify at runtime that the value passing through them is compatible with the specified type. Variables themselves don’t have a type.

Next Time

Congratulations on making it through type system, level one!

While we’ve covered a lot of ground, chances are what we’ve discussed hasn’t been super surprising (except maybe the dynamic type behavior, if you come from a statically typed background). However, hold on to your hats, for when we get to custom types, things may seem counterintuitive.

Before we get to those types, though, we’ll learn about decorating types with informational-only annotations, commonly called facets. In query editor, ever notice that there seem to be four number types (decimal number, currency, whole number and percentage)? These are all really just type number, differentiated by facets. More on this next time.

Until then, have fun mashing up data!

Revision History

2021-09-22: Added a brief mention of type action.