Custom Connectors: Populating Table.Schema

Table.Schema offers to expose a variety of details about a table’s columns. Your custom connector can leverage this functionality to give users easy access to column-specific details from the external data source about the tables they are fetching.

Say that external source allows users to create new columns and give them descriptions. Why not make these descriptions available from within Power Query by having them appear in Table.Schema‘s Description column? Perhaps the type system on the source is a bit different from Power Query’s. Table.Schema columns like NativeTypeName, NumericScale and NumericPrecision can be used to communicate relevant details to your users for reference purposes, enhancing their understanding of the data they’re pulling.

How?

The idea of exposing informative details about a source’s columns is great—but how do you provide this information in a way that Table.Schema will read it? The answer depends on which Table.Schema column you’re trying to populate.

While these techniques may primarily be of interest to custom connector developers, they’re valid to use from any M code—no connector required.

Group 1—Column Rudiments

• Name—Is the column’s name (obviously!).
• Position—Automatically set based on the column’s position in the table’s set of columns.

let
  Table = #table({"Name", "Amount", "Uri"}, {})
in
 Table.Schema(Table)

Group 2—Type Details

All three of these come from the column’s type.

• TypeName—Despite its name, not the column’s actual type’s name, but rather the type claim name associated with the column’s type. At least, this is the theory,but sometimes the default claim associated with the in-use claim’s underlying type is displayed here instead of the actual in-use type claim (for example, unfortunately, a Uri.Type or Guid.Type column will show Text.Type here instead of Uri.Type or Guid.Type, respectively; on the other hand, numeric type claims, like Int64.Type, seem to show up accurately in this column).
  [Related: Type Claims (The Other Facet) from the Power Query M Primer.]
• Kind—A textual rendering of the column’s type name (e.g. “number” if the column’s underlying type is type number).
• IsNullable—Set to true if the column’s type is nullable; otherwise, false.

let
  Type = type table [
    Name = text, 
    Amount = nullable Currency.Type, 
    Uri = Uri.Type
  ],
  Table = #table(Type, {})
in
  Table.Schema(Table)

Group 3—Facets

The next set of columns are all read from the column type’s facets.

• NumericPrecisionBase
• NumericPrecision
• NumericScale
• DateTimePrecision
• MaxLength
• IsVariableLength
• NativeTypeName
• NativeDefaultExpression
• NativeExpression

Facets are set by calling Type.ReplaceFacets on the column type of interest, passing in a record that defines the applicable facet values.

[Related: Type System II – Facets from the Power Query M Primer.]

let
  Type = type table [
      Name = (Type.ReplaceFacets(type text, [IsVariableLength = true, MaxLength = 50])),
      Amount = (Type.ReplaceFacets(Currency.Type, [NumericPrecision = 20, NumericPrecisionBase = 2, NumericScale = 2])),
      Uri = Uri.Type
    ],
  Table = #table(Type, {})
in
  Table.Schema(Table)

Group 4—Metadata

The last set of columns are pulled from metadata.

• Description—Metadata field Documentation.FieldDescription
• IsWritable—Metadata field Documentation.IsWritable
• FieldCaption—Metadata field Documentation.FieldCaption

Metadata is applied to a column type by creating a record with the appropriate fields then using either operator meta or function Value.ReplaceMetadata.

[Related: Metadata from the Power Query M Primer.]

let
  Type = type table [
      Name = (Type.ReplaceFacets(type text, [IsVariableLength = true, MaxLength = 50]),
      Amount = (Type.ReplaceFacets(Currency.Type, [NumericPrecision = 20, NumericScale = 2]),
      Uri = (Uri.Type meta [Documentation.FieldDescription = "URI to product details page"])
    ],
    Table = #table(Type, {})
in
  Table.Schema(Table)