Type.TableSchema

In this article, we’ll take a closer look at the M code behind the Type.TableSchema function, explore its syntax and usage, and provide examples of how it can be used in Power Query.

Understanding the Type.TableSchema Function

The Type.TableSchema function is used to define the schema of a table in Power Query. It is a type function that returns a table schema value, which is used to describe the structure of the table. The syntax of the Type.TableSchema function is as follows:

Let’s break down the parameters of the Type.TableSchema function:

– Columns: A list of columns that define the structure of the table. Each column is defined using the following syntax:

– Name: The name of the column.

– Type: The data type of the column.

– Optional: A logical value that specifies whether the column is optional. If set to true, the column can be null or missing in the table.

– DefaultValue: The default value of the column if it is missing.

– PrimaryKeyColumns: An optional list of primary key columns for the table.

– NavigationProperties: An optional list of navigation properties for the table.

– IsNullable: An optional logical value that specifies whether the table is nullable. If set to true, the table can be null.

– IsOpen: An optional logical value that specifies whether the table is open. If set to true, the table can have additional columns not defined in the Columns parameter.

Examples of Type.TableSchema Usage

Let’s explore some examples of how the Type.TableSchema function can be used to define the schema of a table in Power Query.

Example 1: Defining a Simple Table Schema

Suppose we have a table called “Sales” that contains three columns: “Product”, “Sales Amount”, and “Date”. We can define the schema of this table using the Type.TableSchema function as follows:

In this example, we define the three columns of the “Sales” table. The “Product” column is of type text, the “Sales Amount” column is of type number, and the “Date” column is of type date. Since we don’t specify any primary key columns or navigation properties, they are not included in the schema.

Example 2: Defining a Table Schema with Primary Key Columns

Suppose we have a table called “Customers” that contains three columns: “Customer ID”, “Name”, and “Address”. The “Customer ID” column is the primary key for the table. We can define the schema of this table using the Type.TableSchema function as follows:

In this example, we define the three columns of the “Customers” table. We also specify that the “Customer ID” column is the primary key for the table by including it in the PrimaryKeyColumns parameter.

Example 3: Defining a Table Schema with Navigation Properties

Suppose we have two tables called “Orders” and “Customers”. The “Orders” table contains four columns: “Order ID”, “Customer ID”, “Product”, and “Order Date”. The “Customer ID” column references the “Customers” table. We can define the schema of the “Orders” table, including the navigation property to the “Customers” table using the Type.TableSchema function as follows:

In this example, we define the columns of the “Orders” table. We specify that the “Customer” column has a navigation property to the “Customers” table by defining the column as a table schema value and including the primary key column of the “Customers” table.

In conclusion, the Type.TableSchema function is an essential tool for defining the schema of tables in Power Query. By understanding the M code behind this function and its various parameters, users can easily define and manipulate table schemas to suit their needs. With the examples provided in this article, users can begin to explore the vast potential of the Type.TableSchema function in Power Query.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us