Power Query M Primer (Part 17): Type System II – Facets

In query editor, ever notice that the column data type menu includes four options for numbers: Decimal Number, Currency, Whole Number and Percentage? In this series, we’ve only talked about one numeric type: type number. Are there types we’ve missed?

Behind the scenes, menu item Decimal Number maps to type number, Currency to Currency.Type, Whole Number to Int64.Type and Percentage to Percentage.Type. If you look at the names defined in your Power Query environment, you’ll likely see a host of other “Type” names, including Int8.Type, Int16.Type, Int32.Type, Single.Type and Double.Type. What are all these “Type” names—even more types we have yet to cover?!

Nope! Introducing type facets.

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)
• Type System II – Facets (part 17) [this post]
• 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 Facets

Power Query allows a type to be decorated with informational-only annotations, commonly known as facets.

Facets have no intrinsic effect on the behavior of types or on the values associated with them at either the language or mashup engine levels. Technically, logic you and others write (including the standard library) can read and react to facets, though this is done rarely (if ever). Instead, facets are used almost exclusively in the context of interacting with the external (data sources, tools, the host environment). External systems often have more complex type systems than Power Query. Facets provide a way to communicate extra type-related details to and from the outside world.

Imagine you’re working with a web service that returns a table containing a text column and a number column. M is satisfied knowing that the values in these columns are of type text and of type number, respectively. However, it might help you as you build your mashup to know that the text column can contain values of variable length, up to 25 characters long, and that the number column contains integers, not decimal values. These extra details, provided by the data connector using facets, make no difference in how the mashup engine processes the data—but they could be helpful to you, as the developer.

Going the other direction, when your mashup outputs data to the host environment (Microsoft Power BI, Microsoft Excel, etc.), providing enhanced type information about the values being produced could influence how the external environment handles what it receives. Take a mashup that outputs a table containing a numeric column to Microsoft Power BI. As far as the Power Query language goes, all numbers are of type number. However, Power BI supports several number types. If Power BI is informed via a facet that the values in the column are all integer numbers, it can set the column’s Power BI storage type to whole number, saving you the work of doing this manually.

Tools can also use facet information. Jumping back to our starting scenario: Query Editor (a tool) features four so-called number types in its menus and icons. Ultimately, all of these are type number, differentiated by facets.

As was already mentioned, facets are primarily used in interactions with the external. Since tables are the main form of input and output exchanged with outside world, facets are primarily of interest when working with tables. However, no technical limitations stop facets from being used with other types.

There are two sets of facets: the simple ones to work with and the other one. How’s that for refined technical speak?! (The language specification doesn’t give these groupings names. In fact, the specification doesn’t mention facets at all, even though they are a part of the information contained in type values.) 

Simple Facets

The simple facets are almost exclusively used by data connectors to provide extra information about the values they produce. While technically these facets can be programmatically read and processed, they are primarily targeted for human (developer) consumption—giving you a peak into how the remote system thinks about the data it’s handing your mashup.

Unless you build data connectors, you may never find yourself with a production need to set these facets; however, learning how to set them should help with understanding them, so let’s give it a whirl.

To define values for the simple facets, use Type.ReplaceFacets, passing it a type value and a record with the facets you want to set on that value. The record should be in the following format, with any facets not of interest set to null or left out of the record:

[
  NumericPrecisionBase = ..., // number
  NumericPrecision = ..., // number
  NumericSale = ..., // number
  DateTimePrecision = ..., // number
  MaxLength = ..., // number
  IsVariableLength = ..., // logical
  NativeTypeName = ..., // text
  NativeDefaultExpression = ..., // text
  NativeExpression = ... // text
]

The below sets facets MaxLength, IsVariableLength and NativeTypeName on a type text type value.

let
  SomeType = type text,
  Facets = 
    [ 
      MaxLength = 25,
      IsVariableLength = true, 
      NativeTypeName = "NVARCHAR"
    ],
   TypeWithFacets = Type.ReplaceFacets(SomeType, Facets)
in
  TypeWithFacets 

Important: TypeWithFacets is still a type value holding type text. Setting facets on it did not create a new type. Attaching those facets simply produced a new type value representing the existing type decorated with several informational-only annotations. As far as the mashup engine’s processing goes, the type in this new type value is still type text.

While occasions to use Type.ReplaceFacets may be extremely few and far between, you may find yourself reading facets slightly more often. Type.Facets is one way to do this. This method returns a record describing the simple facets that decorate the specified type value.

Using Type.Facets with TypeWithFacets (from the above example), we can see the simple facets that were just set:

Type.Facets(TypeWithFacets) 
/* outputs:
[
  NumericPrecisionBase = null,
  NumericPrecision = null,
  NumericScale = null,
  DateTimePrecision = null,
  MaxLength = 25,
  IsVariableLength = true,
  NativeTypeName = "NVARCHAR",
  NativeDefaultExpression = null,
  NativeExpression = null
]
*/

Pretend that we didn’t create TypeWithFacets. Instead, pretend it came from a value that was handed to us by a data connector. Using the information in these facets, we can conclude that it probably represents data from a NVARCHAR(25) database column. This information might help in our development, cluing us that we may receive characters spanning the full Unicode range but the strings they make up won’t be super long (at most, 25 characters). Even though we may have no direct access to the remote system, the information in these facets gives us a glimpse into how that system thinks about the data it sends our way, which may help clear up unknowns for us.

Type.Facets lets us view the simple facets associated with a type, one type at a time. To see the facets associated with each column in a table, we could pass each column’s type to Type.Facets—or we could try Table.Schema. This function lets us see all facets (the simple ones and the other one ) for each column, along with several other descriptive pieces of information, all at once.

Table.Schema(SomeTable)

Above, the simple facets are spread across several columns (circled in blue). The other facet—the one we haven’t described about yet—appears in this table, under the confusing name of TypeName (circled in red). Actually, M’s base type for the column is shown in column Kind (circled in green), not TypeName. TypeName here does not identify an M type but rather a type claim facet.

 Type Claims (The Other Facet)

Power Query uses a very general set of types for simple values: all numbers are of type number, all strings are of type text, etc. External systems may have types which divide values into more specific classifications. For example, instead of a one-size-fits-all numeric type, an external system might have integer, float and decimal types. It might even subdivide these further, say splitting integers between 8-, 16-, 32- and 64-bit variants.

While such nuanced divisions are irrelevant as far as executing mashups go—all Power Query cares about are its types—usually, what’s output ends up being handed to an external system. A way to provide that system with enhanced type guidance could help it optimally handle and store the values it receives.

Data outputted by mashups often originates from external systems. Ideally, the mechanism used for enhanced type guidance would be something that data sources could also participate in, so that they can annotate data they provide with extra type details that will stay with that data as it flows through Power Query and out to the ultimate external destination.

Simple facet NativeTypeName may come to mind. However, it’s not helpful here because its values are external system specific and so are not interchangeable between different external systems. Instead, what’s needed is an external system agnostic way to communicate relevant type details.

Type claim facets provide a standardized way to identify common external type groupings using a system-agnostic nomenclature.

Data connectors that provide type claims map between their external system’s types and the appropriate type claim names. Data destinations (e.g. the host environment) map these claims to their type systems and other settings, as appropriate. Tooling can also read these claims and react accordingly, such as how Query Editor may change a table column’s icon based on the column type’s type claim.

Unlike the simple facets, which default to null if not set, every type has a type claim. By default, this claim corresponds with the type’s base name. For example, type text’s default claim is “Text.Type” and type date’s is “Date.Type”. These defaults, by themselves, aren’t super useful; it’s the fact that other type claims can be announced that makes this facet interesting and helpful.

Type claim names are predefined. You can’t set this facet by specifying an arbitrary value, like you can with the simple facets. Instead, the standard library provides predefined type values annotated with the various applicable type claim facets. To use a type decorated with one of these claims, simply fetch the predefined type value that’s already associated with the type claim facet of interest. 

Type Names

Now we’re ready to unravel the mystery of the “Type” names provided by the standard library (Int8.Type, Currency.Type, Single.Type, etc.).

Don’t let “Type” in the names confuse you. When you create something in query editor, you define an expression that returns a value—and you give that expression a name. Your choosing a name that ends in “Type” doesn’t create a new language-level type, neither does the fact that the standard library gives things names that end with “Type” cause new types to exist.

Instead, these “Type” names are simply names associated with expressions that return the appropriate type to use for whatever the name describes, decorated with the applicable type claim facet. For example, Int8.Type is a name associated with an expression that returns a type value of the correct type to use for 8-bit integers (type number) decorated with a type claim facet of “Int8.Type”; Int.16.Type returns a type value holding type number decorated with an “Int16.Type” type claim, and so on for Int32.Type, Int64.Type, Currency.Type, Percentage.Type, etc.

 Viewing

Type claims are used so little outside of tables that the only built-in way to view this facet is via the standard library’s table functionality. However, since we’re learning about type claims in general vs. table types in particular, it would be nice to have an easy way to view the type claim facet associated with any type, including non-table types.

Try the following helper function, which encapsulates the (slight) complexity of using table functionality to extract the type claim off any type value. (Note: This function uses some syntax that we haven’t covered yet. Details coming soon, in the next post!)

TypeClaimFacet = (input as type) as text => Table.SingleRow(Table.Schema(#table(type table [Col1 = input], {})))[TypeName]

Applying this helper to some type values lets us see the associated type claim facet values:

let
  TypeClaimFacet = (input as type) as text => Table.SingleRow(Table.Schema(#table(type table [Col1 = input], {})))[TypeName]
in
  TypeClaimFacet(type number) // "Number.Type" -- the default type claim for type number
//  TypeClaimFacet(Int8.Type) // "Int8.Type"
//  TypeClaimFacet(Int16.Type) // "Int16.Type"
//  TypeClaimFacet(Currency.Type) // "Currency.Type"

Ascribing Types

In Power Query, every value has an ascribed type, which is simply the type to which the value is declared to conform. When a value is created, Power Query automatically ascribes it with an intrinsic type: a numeric value is ascribed with type number when it’s created, a string value is ascribed with type text, and so forth—nothing surprising here.

M’s immutability means you cannot modify the type ascribed to a value. If this is where things stopped, type facets would be pointless because you couldn’t associate a non-default facet with a value.

Instead of modifying the type associated with a value (which isn’t allowed), what you can do is replace it using Value.ReplaceType. This method outputs a new value that’s the same value as before except it’s now associated with—it’s been ascribed with—the new type.

// ascribing a type having a non-default type claim facet
let
  StartingValue = 1, // we know this is of type number
  Result = Value.ReplaceType(StartingValue, Int64.Type)
in
  Result // the value 1, of type number, decorated with an "Int64.Type" type claim facet

// ascribing a type having a simple facet
let
  StartingValue = 1, // we know this is of type number
  OriginalType = Value.Type(StartingValue),
  Faceted = Type.ReplaceFacets(OriginalType, [NativeTypeName = "INT"]),
  Result = Value.ReplaceType(StartingValue, Faceted)
in
  Result // the value 1, of type number, decorated with a NativeTypeName = "INT" facet

// ascribing a type having both a type claim and a simple facet
let
  StartingValue = 1, // we know this is of type number
  Faceted = Type.ReplaceFacets(Int64.Type, [NativeTypeName = "INT"]),
  Result = Value.ReplaceType(StartingValue, Faceted)
in
  Result // the value 1, of type number, decorated with a type claim facet of Int64.Type and a NativeTypeName = "INT" facet

Above, when we described ascribing types (a.k.a. replacing types), we used the word “type” in an overloaded sense, referring to everything that can be contained in a type value. The previous examples did not change the value’s base type from number to some other base type, but rather replaced the value’s plain-vanilla type number with a type number value decorated with a different facet (or facets).

The type being ascribed must always be structurally compatible with the value it’s being ascribed to. So, number values can only be ascribed number type values, text values can only be associated with text type values, etc. You can’t use Value.ReplaceType to coerce a number value to be of type text or vice versa.

How type ascription works when custom types are involved is complex and perhaps counterintuitive—but still complies with the rule that the ascribed type must be structurally compatible with the value. More on this next time. 

Claiming vs. Converting

To repeat a key point: Type facets have no mashup engine or language-level effects on behavior. In particular, facets do not define or create new types or sub-types—nothing like that. They’re just informational-only annotations.

A value of type number is a number and will behave like any other number, regardless of whether the value’s type is faceted with a claim that it is Int16.Type or Percentage.Type or any other claim. 1 + 2 will always equal 3, regardless of what facets may be associated with each of those values.

The below value is not a 64-bit integer, as it is both outside the range of signed whole number values that can be represented in 4-bytes and contains a decimal component. However, no complaints are raised if it is ascribed with a type claim facet of Int64.Type—because facets are strictly informational declarations, not validated facts.

Value.ReplaceType(9223372036854775808.01, Int64.Type)

On the other hand, in query editor, if you use a table column’s “change type” menu to set a column to whole number (which maps to Int64.Type), the presence of 9223372036854775808.01 in that column will cause an error complaining about the value being “out of range of a 64 bit integer value.” This seems to contradict the “no effects on behavior” statement. What’s going on?

Here’s a mashup showing the Query Editor-generated change type operation:

let
    Data = #table({"Col1"}, {{9223372036854775808.01}}),
    #"Changed Type" = Table.TransformColumnTypes(Data,{{"Col1", Int64.Type}})
in
    #"Changed Type"

Change Type‘s Table.TransformColumnTypes does two things: sets the column type to the appropriate M type, decorated with the appropriate type claim, and attempts to transform each value in the column by converting it to the appropriate type then validating that it complies with the specified type claim.

Power Query uses a set of functions whose names end in “.From” to perform these value transformations. You can directly play with these functions to see how the conversion process works.

For example, in the case of Int64.From, first the input value is converted to type number (if it isn’t already a number) and any decimal component is rounded. Then, the resulting whole number is checked to see whether it is within the range of valid values for a 64-bit integer. If it is, the number is returned. If not, an error is raised.

Int64.From(9223372036854775808.01) // raises 'Expression.Error: The number is out of range of a 64 bit integer value.'
Int64.From(9223372036854775807.01) // returns 9223372036854775807 -- notice how the fractional component was rounded

The conversion process is what raises errors about values being outside the expected range, not type ascription. (In fact, from what I’ve seen, the various “.From” methods don’t perform any special type ascription. For example, the result returned by Int64.From is not associated with an “Int64.Type” type claim facet.)

In addition to converting values, Table.TransformColumnTypes sets columns type appropriately, as already mentioned. We can observe this by turning to our friend, Table.Schema. Below, notice how Col1 now has a type claim of “Int64.Type” (as reflected in the confusingly named TypeName but-really-is-type-claim column).

let
    Data = #table({"Col1"}, {{9223372036854775808.01}}),
    #"Changed Type" = Table.TransformColumnTypes(Data,{{"Col1", Int64.Type}})
in
    Table.Schema(#"Changed Type")[[Name], [TypeName]]
// returns a table with a single row equal to [Name = "Col1", TypeName = "Int64.Type" ]

[Reference: Table.TransformColumnTypes use of “.From” functions is confirmed in a TechNet forum thread by a Power Query team member.]

Transform or Just Ascribe?

When you only think (or hope) that column values will comply with a type claim, Table.TransformColumTypes is the recommended approach for setting this facet. Table.TransformColumTypes may be able to fix non-compliant values when it tries to convert them (such as how fractional components are rounded off decimal numbers when they are converted to integers). When it can’t, the errors raised hopefully will alert you to the problem and possibly can be handled by other code you write (e.g. a later use of Table.ReplaceErrorValues).  

On the other hand, if you’re guaranteed that values will comply with the claim (such as when authoring a data connector that can determine this using the external system’s metadata), by all means, ascribe the appropriate type facets to the table’s columns without conversion. There’s no need to pay the processing cost for an unnecessary execution of Table.TransformColumnTypes. (How to ascribe facets to column types involves custom types, which we’ll cover next time.)

External Systems & Type Claim Facets

Why does type claim facet compliance matter, if facets have no behavioral effects, as far as the language and mashup engine are concerned?

M itself does not validate compliance but others might. Most commonly, this occurs when data is handed to external systems. In Power BI’s query editor, suppose you create a table column containing numbers and ascribe the column a type facet claiming Int64.Type. Power Query doesn’t care whether the column’s values actually comply with this claim. However, when the table is handed to the host environment, this claim will guide Power BI to set the storage type it uses for the column to whole number. Then, because of the storage type being used, import of rows where the column contains a non-whole number (based on Power BI’s definition of that term) will error.

Always tell the truth, even if no one will ever find out is a good maxim to apply here. Always do your best to ensure that any type claim facets your mashups output are true. If you’re not sure, better to leave off the claim, even if the external tool you’re using doesn’t seem to care at the moment.

Lifespan

If you have a value whose type is faceted, do those facets survive across operations and function calls?

Say you apply an operator between a values which are associated with a non-default type claim facet. Will that facet carry over to the operator’s output?

let
  ValueA = Value.ReplaceType(1, Int64.Type),
  ValueB = Value.ReplaceType(2, Int64.Type),
  Result = ValueA + ValueB
In
  Result // 3 -- but is this number's type associated with an Int64.Type type claim facet?

No, and it shouldn’t. Facets have no effect on language and mashup engine behavior. Adding two number values produces a new number value which will have the default type assigned to all numeric values (type number) without any facets carried over. You might be adding two numbers that comply with their Int64.Type type claims. However, this doesn’t mean that their sum is guaranteed to comply with that claim, so it shouldn’t automatically be associated with it.

On the other hand, facets may be “carried across” function calls. Typically, you’ll see this with table functions. Some list functions do it, as well. What happens is that the function reuses the column types (table functions) or list item type (list functions) associated with its input for its output. Since those types are preserved, any associated facets stay with them. For example, if you apply Table.Sort to a table, column types are preserved across the sort, so any facets associated with those column types are preserved.  

Conclusion

Hopefully, it’s been at least somewhat fascinating to study facets (sorry—poor attempt at a pun!).

In this article, we worked a bit with facets decorating types for simple values (e.g. numbers). This allowed us to explore facets without the complexity of table types. However, remember that type facets are primarily relevant when interacting with the external (data sources, host environment and tools). Since tables are the main means of data exchange with the outside world, facets are almost exclusively used with tables.

To re-emphasize two key points: Facets have no effect on behavior at either the language or mashup engine layers. Applying facets and defining names that end with “Type” do not create new types or sub-types.

Next Time

Tables, functions, records and lists—all of these use, or can use, custom types to describe their specifics. Next time, we’ll learn how to define and work with custom types. Get ready for a surprise: type ascription and compatibility checking might not work the way you’d expect for custom types.

Until then, keep on having fun with Power Query!

Revision History

2020-03-06: Updated section Claiming vs. Converting, removing the statement that I was unable to officially say that Table.TransformColumnTypes use “.From” functions, as their use has been officially confirmed.