Table.Schema Function in Power Query M (4 Easy Examples)

If you want to inspect a table’s column structure programmatically, like the column names, their positions, and their types, the Table.Schema function is what you reach for.

It hands you back a small table describing every column, which is handy for documenting a query or checking how a source defined its columns. In this article, I’ll show you how to use Table.Schema with a few practical examples.

Table of Contents

Syntax of Table.Schema Function

Table.Schema(table as table) as table

where

table (required, table). The table whose column structure you want to describe.

Returns: a table with one row per column of the input, listing that column’s name, position, type, nullability, and other type metadata.

In plain terms, you give it a table, and it gives you back a description of that table’s columns rather than its data.

Example 1: Get the full schema of a typed table

Say you have a Products table and you want to see how Power Query describes its columns.

A column’s type info only means something once the column is typed, so type the columns first with Table.TransformColumnTypes, then read the schema.

Here is the starting data:

Product	Category	Units
Notebook	Stationery	120
Marker	Stationery	80
Folder	Office	45

Type the columns, then call Table.Schema on the typed table:

let
    Source = Excel.CurrentWorkbook(){[Name="Products"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Product",type text},{"Category",type text},{"Units",Int64.Type}}),
    Schema = Table.Schema(Typed)
in
    Schema

The result has one row per column. Here are the key columns it returns:

Name	Position	TypeName	Kind	IsNullable
Product	0	Text.Type	text	true
Category	1	Text.Type	text	true
Units	2	Int64.Type	number	true

The full schema also has columns like NumericPrecision, MaxLength, NativeTypeName, and Description, but those depend on the engine and the source, so the reliable ones to lean on are Name, Position, and Kind.

Example 2: Reduce the schema to the columns you care about

The full schema is wide, so most of the time you keep just the columns you need.

Suppose you only want Name, Position, and Kind for the same typed Products table.

Here is the starting data:

Product	Category	Units
Notebook	Stationery	120
Marker	Stationery	80
Folder	Office	45

Type the columns, take the schema, then pick the three columns with Table.SelectColumns:

let
    Source = Excel.CurrentWorkbook(){[Name="Products"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Product",type text},{"Category",type text},{"Units",Int64.Type}}),
    Schema = Table.Schema(Typed),
    Reduced = Table.SelectColumns(Schema,{"Name","Position","Kind"})
in
    Reduced

The result is a tidy column inventory:

Name	Position	Kind
Product	0	text
Category	1	text
Units	2	number

Notice that Kind reports the simplified type family (text, number), which is why typing the columns first matters. On an untyped load, every Kind would read any.

Example 3: Check whether a single column allows nulls

You can drill into one column’s metadata by filtering the schema down to that column and reading the field you want.

Say you want to know whether the Units column can contain null values.

Here is the starting data:

Product	Category	Units
Notebook	Stationery	120
Marker	Stationery	80
Folder	Office	45

Filter the schema to the Units row, then read its IsNullable value:

let
    Source = Excel.CurrentWorkbook(){[Name="Products"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Product",type text},{"Category",type text},{"Units",Int64.Type}}),
    Schema = Table.Schema(Typed),
    UnitsRow = Table.SelectRows(Schema,each [Name]="Units"),
    IsNullable = UnitsRow{0}[IsNullable]
in
    IsNullable

Result: true

Ascribing a base type like Int64.Type produces a nullable column, so IsNullable is true. To get a non-nullable column you would declare the type explicitly.

Example 4: Get the column names as a quick list

If all you want is the list of column names, project the schema’s Name column with [Name].

This works straight off the raw source, so there is no need to type the columns first.

Here is the starting data:

Product	Category	Units
Notebook	Stationery	120
Marker	Stationery	80
Folder	Office	45

Pull the Name column off the schema:

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

Result: {"Product","Category","Units"}

The [Name] projection turns the schema’s Name column into a flat list, which gives you the headers in their original order.

Things to keep in mind with Table.Schema

It describes the column types you ascribe, not the data. A freshly imported Excel or CSV table loads every column as any, so type it with Table.TransformColumnTypes first or the schema’s type columns are all any/null.
Name and Position are always reliable. They come straight from the table structure. The type columns (TypeName, NativeTypeName, the numeric fields) depend on the engine and the source.
Kind is the simplified type family, TypeName is the precise type. Kind reads text/number/datetime, while TypeName carries the exact M type like Text.Type or Int64.Type.
For just the names, Table.ColumnNames is simpler. It returns the list of headers directly. Reach for Table.Schema(t)[Name] only when you are already pulling other metadata.

Common questions about Table.Schema

What is the difference between Table.Schema and Table.ColumnNames?

Table.ColumnNames returns only the list of column names. Table.Schema returns a full table of metadata per column (position, type, nullability, and more), so use it when you need more than the names.

How is Table.Schema different from Table.Profile?

Table.Schema describes the columns’ structure (names, types, nullability). Table.Profile describes the data inside the columns (min, max, average, distinct counts), so one tells you the shape and the other tells you the statistics.

List of All Power Query Functions

Related Power Query Functions / Articles: