Table.Schema
Returns a table that describes each column, including its name, its type and whether it allows blanks.
Table.Schema(table as table) as tableReturns: 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
- table: the table to describe. Required.
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
- Schema describes what the query knows at that step. An untyped column can appear as a generic type, so type your columns first.
- Use this step for checking and documentation, not for building the report.
- The metadata comes from the query, not the file on disk, so a change to the source is reflected only after a refresh.
- Query folding: this step does not change the data. It is a check of the query's structure.
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.