Downloads
Power Query MTable: columnsAdvanced

Table.Schema

Returns a table that describes each column, including its name, its type and whether it allows blanks.

Syntax
Table.Schema(table as table) as table

Returns: A table with one row for each column.

What it does

Table.Schema reports the structure of a table: each column's name, its position and its data type. It is the way to confirm that a type step did what you intended, or to produce a list of fields for documentation. It describes the table at the point of the step, so place it after the typing step you want to check.

Syntax and arguments

Example

After the types are set, a data dictionary of the bank export is built from its schema, with the field name and the type name for each column.

let
    Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
    Typed = Table.TransformColumnTypes(Source, {{"Date", type date}, {"Description", type text}, {"Amount", type number}}),
    Dictionary = Table.SelectColumns(Table.Schema(Typed), {"Name", "TypeName"})
in
    Dictionary

The result is a table of field names and their types. A reviewer can compare it with what the report expects.

Example: a second, different use

Check a column that should be a date before it reaches a model. The schema is read, and the result is kept for the preparer to inspect.

let
    Source = Excel.CurrentWorkbook(){[Name = "Invoices"]}[Content],
    Schema = Table.Schema(Source)
in
    Schema

Things to watch

In business terms

A data dictionary tells a new team member what each field is and what it holds. Generating it from the query means the dictionary matches what the report really uses.

Related functions

All Power Query M functions · View as Markdown · M basics