Type.TableSchema

Type.TableSchema is a Power Query M function that returns a table describing the columns of a given table type. The function returns a table with information about the columns in the input table type, as described in the Table.Schema documentation.

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

Syntax

Type.TableSchema( tableType as type ) as table

Column Name	Description
Name	The column name.
Position	The 0-based position of the column in table.
TypeName	The name of the type of the column.
Kind	The kind of the type of the column.
IsNullable	Whether the column can contain null values.
NumericPrecisionBase	The numeric base (for example, base-2 or base-10) of the NumericPrecision and NumericScale fields.
NumericPrecision	The precision of a numeric column in the base specified by NumericPrecisionBase. This is the maximum number of digits that can be represented by a value of this type (including fractional digits).
NumericScale	The scale of a numeric column in the base specified by NumericPrecisionBase. This is the number of digits in the fractional part of a value of this type. A value of 0 indicates a fixed scale with no fractional digits. A value of null indicates the scale is not known (either because it is floating or not defined).
DateTimePrecision	The maximum number of fractional digits supported in the seconds portion of a date or time value.
MaxLength	The maximum number of characters permitted in a text column, or the maximum number of bytes permitted in a binary column.
IsVariableLength	Indicates whether this column can vary in length (up to MaxLength) or if it is of fixed size.
NativeTypeName	The name of the type of the column in the native type system of the source (for example, nvarchar for SQL Server).
NativeDefaultExpression	The default expression for a value of this column in the native expression language of the source (for example, 42 or newid() for SQL Server).
NativeExpression
Description	The description of the column.
IsWritable
FieldCaption

Description

The Type.TableSchema function returns a table describing the columns of the input tableType. It returns similar columns as the Table.Schema function. The operation returns the following fields:

Examples

Suppose you have a table in a step called source. To describe the columns in that table, you can use the Type.TableSchema function.

First, you need to find out the type of the table. If your table is named Source, you can use the Value.Type function like this:

Value.Type( Source )

Next, pass the table type to the Type.TableSchema function:

Type.TableSchema( Value.Type( Source ) )

This returns a table with 17 columns, describing the contents of your table.

Converting Data Types to Text

The Type.TableSchema function is also handy when you want to convert a data type into text. You might think to use functions like Text.From to get a text value from a data type, but this returns the error: “”Expression.Error: We cannot convert Type to Text type.”

Instead, you can use the following approach:

// Returns: "This sentence refers to a 'text' type"
let
    myType = type text,
    myTableType= type table[ c = myType ],
    tableSchema = Type.TableSchema( myTableType),
    typeAsText = tableSchema[Kind]{0},
    SentenceIncludingType = "This sentence refers to a '" & typeAsText & "' type"
in
    SentenceIncludingType

How does this work?

1. Define the Data Type: Stores the text data type: type text.
2. Create a Table Type: type table[c = myType] creates a table with a column named c.
3. Get the Schema: Type.TableSchema(myTableType) retrieves the table’s schema.
4. Extract the Data Type as Text: tableSchema[Kind]{0} retrieves the first value of the ‘Kind’ column.
5. Create a Sentence: "This sentence refers to a '" & typeAsText & "' type" includes the data type in a sentence.

Related articles

Learn more about Type.TableSchema in the following articles:

• How to Convert a Data Type to Text in Power Query M
  The article shows how you can convert a data type into its textual representation. » Read more

Related functions

Other functions related to Type.TableSchema are:

• Type.TableColumn
• Type.TableRow

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