Type.ReplaceFacets

Updated on

Type.ReplaceFacets is a Power Query M function that replaces the facets of a given type with the facets contained in a record. The function returns a modified type with the new facets applied.

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

Syntax

Type.ReplaceFacets(
   type as type,
   facets as record,
) as type

Description

The Type.ReplaceFacets function takes a type as input and replaces its facets with the facets provided in the 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 as 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.ReplaceFacets 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.

Using Type.Facets on a type text value

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 providing 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

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
]

Learn more about Type.ReplaceFacets in the following articles:

Other functions related to Type.ReplaceFacets are:

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