Table.Schema

In this article, we will take a deep dive into the M code behind the Power Query M function Table.Schema. We will explore how this function works, how to use it effectively, and some common use cases.

What is Table.Schema?

The Table.Schema function in Power Query is used to inspect the structure of a table. It returns a record that describes the schema of the table, including information about the columns, data types, and other metadata. The schema record is returned as a single row table, which can then be used for further analysis and transformation.

The Table.Schema function takes a single argument, which is the table that you want to inspect. The function returns a record with the following fields:

– Name: The name of the table.

– Columns: A list of records describing each column in the table.

– NavigationProperties: A list of records describing any navigation properties in the table.

– Meta: A record containing any additional metadata associated with the table.

How to Use Table.Schema

Using Table.Schema is straightforward. Simply pass the table that you want to inspect as an argument to the function. The resulting schema record can then be used for further analysis and transformation.

Here’s an example of how to use Table.Schema:

In this example, we create a new table called Source, which contains two columns. We then pass Source as an argument to the Table.Schema function, which returns a schema record describing the structure of the table. The resulting schema record is then returned as output.

Common Use Cases for Table.Schema

Table.Schema has many use cases, including:

Inspecting Table Columns

One of the most common use cases for Table.Schema is inspecting the columns of a table. The Columns field in the schema record returned by Table.Schema contains a list of records describing each column in the table. This can be useful for identifying data types, column names, and other metadata associated with the table.

Identifying Navigation Properties

Table.Schema can also be used to identify navigation properties in a table. The NavigationProperties field in the schema record contains a list of records describing any navigation properties in the table. These can be used to identify relationships between tables and other metadata associated with the table.

Data Type Conversion

Table.Schema can also be used to convert data types in a table. By inspecting the data types of each column using the schema record returned by Table.Schema, you can identify columns that need to be converted to a different data type. This can be especially useful when working with large datasets where manual data type conversion would be time-consuming.

In this article, we have explored the M code behind the Power Query M function Table.Schema. We have seen how this function works, how to use it effectively, and some common use cases. By using Table.Schema to inspect the structure of a table, you can gain valuable insights into the metadata associated with the table and use this information to further analyze and transform your data.

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

Upcoming Courses

Contact Us