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 typeDescription
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.
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
addFacetsThe 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.ReplaceFacets 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.ReplaceFacets are:
