Type.Facets

Type.Facets is a Power Query M function that retrieves a record containing the facets of a given type. The function returns a record with the facets information of the input type.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Type.Facets( type as type ) as record

Description

The Type.Facets function is used to identify facets associated with a given type and return them in the form of a record.

Facets are particularly useful for conveying additional information about data types, especially when interacting with external systems like data sources or tools. They are particularly important for table types, as tables are the primary mode of data exchange with external sources. Facets allow external sources to provide additional type information to a type, which can be useful for data handling and storage.

While facets don’t inherently change the behaviour of types within Power Query, they are useful for external interactions (such a Power BI), offering more information on how data is perceived and managed outside of Power Query. For example, knowing that a text column can hold up to 25 characters or that a number column contains integer values can be used for storing data efficiently.

Examples

Let’s delve into a few examples to understand the Type.Facets function.

Purpose of Type.Facets

Imagine you’re using type text to describe some of your values. The Type.Facets function allows you to see the facets that are assigned to a data type. By default, this type does not have any facets associated with it. You can test that by writing:

Type.Facets( type text )

This expression returns a record containing the different facet values for type text.

Adding Facets to Types

Although simple data types in Power Query don’t come with predefined facets, you can add them using the Type.ReplaceFacets function. This is useful for detailing extra information about data types that can be instrumental when working with complex systems.

For instance, you can add facets to define maximum length, variable length, and native type name. Here’s how you do it:

let
  myType = type table,
  myFacets = 
    [
      MaxLength        = 25,  
      IsVariableLength = true,  
      NativeTypeName   = "NVARCHAR" 
    ],
  addFacets = Type.ReplaceFacets( myType, myFacets )
in
  addFacets

Once you apply this operation, you can view the set facets using Type.Facets:

Type.Facets( addFacets )

This will show a record of the facets you’ve just added to myType.

The above example set but a few values. While typically external sources provide Facet information to a type, you are able to add facets as described above. Here’s an overview of the different types of values each facet expects.

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

Related articles

Learn more about Type.Facets in the following articles:

• Power Query M Primer (Part 17): Type System II – Facets
  This article describes how facets can convey additional information for type values in Power Query. » Read more

Related functions

Other functions related to Type.Facets are:

• Type.NonNullable
• Type.ReplaceFacets
• Type.Union

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/type-facets