Table.Schema - Table Function

Table.Schema is a Power Query M function that generates a table describing the properties of the input table’s columns, including column name, position, type, and other metadata. The function returns a table with the column properties.

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

Syntax

Table.Schema( table as table ) as table

Description

Returns a table describing the columns of table.

Each row in the table describes the properties of a column of table:

Column Name	Description
`Name`	The name of the column.
`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).

`Description`	The description of the column.

Examples

The Table.Schema function returns schema information from a table. This can be useful to inspect the contents of a table, but also to return specific information.

For instance, if you want to return the textual representation of a data type, you could store it within a table and then use Table.Schema to pick up the relevant data type as text.

Here’s how:

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

Learn more about Table.Schema 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

Other functions related to Table.Schema are:

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

Syntax

Description

Examples

Related articles

Related functions