Type.TableColumn

In this article, we will take a closer look at the M code behind the Type.TableColumn function in Power Query.

What is Type.TableColumn?

Type.TableColumn is a M function used to determine the data type of a specific column in a table. It takes in two arguments: a table, and a column name or index.

The function returns a record with the following fields:

– Type: the data type of the column (e.g. text, number, date)

– Culture: the culture used for formatting the column

– DefaultValue: the default value of the column

– IsNullable: whether the column allows null values

– Precision: the precision for numeric columns

– Scale: the scale for numeric columns

How Does Type.TableColumn Work?

To use Type.TableColumn, you first need to create a reference to the table in Power Query. This can be done by selecting the table in the workbook, and clicking on the “From Table/Range” button in the “Data” tab.

Once you have a reference to the table, you can use the Type.TableColumn function to determine the data type of a specific column. Here is an example:

In this example, we first create a reference to the “MyTable” table using the Excel.CurrentWorkbook function. We then pass this reference, along with the name of the column we want to check (“MyColumn”), to the Type.TableColumn function.

The function returns a record with the data type, culture, default value, nullability, precision, and scale of the “MyColumn” column.

Using Type.TableColumn in Data Transformations

Type.TableColumn can be used in a variety of data transformation scenarios. For example, you can use the function to filter out columns with a specific data type, or to convert columns to a different data type.

Here is an example of how to filter out columns with a data type of “text”:

In this example, we first create a reference to the “MyTable” table using the Excel.CurrentWorkbook function. We then use the Table.SelectColumns function to filter out any columns with a data type of “text”. The Type.TableColumn function is used in the filter expression to determine the data type of each column.

Type.TableColumn is a powerful M function in Power Query that allows you to determine the data type of a specific column in a table. It can be used in a variety of data transformation scenarios, including filtering out columns with a specific data type, or converting columns to a different data type.

By understanding the M code behind Type.TableColumn, you can take full advantage of the advanced data transformation capabilities of Power Query.

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

Upcoming Courses

Contact Us