Table.TransformColumnTypes

In this article, we will delve into the M code behind the Table.TransformColumnTypes function and explore some of its features.

Understanding the basics of the Table.TransformColumnTypes function

The Table.TransformColumnTypes function is used to modify the data type of one or more columns in a table. It takes two arguments: the first argument is the table to be modified, and the second argument is a list of column specifications.

Each column specification is a record that contains the name of the column and its new data type. For example, if we want to change the data type of the “Price” column in a table to Decimal, we would use the following code:

This code changes the data type of the “Price” column in the “SourceTable” to Decimal.

Changing multiple column data types

The Table.TransformColumnTypes function can be used to change the data type of multiple columns at once. To do this, we simply add more column specifications to the list.

For example, if we want to change the data type of both the “Price” and “Quantity” columns in a table to Decimal, we would use the following code:

This code changes the data type of both the “Price” and “Quantity” columns in the “SourceTable” to Decimal.

Handling data type errors

When using the Table.TransformColumnTypes function, it is important to handle any data type errors that may occur. For example, if a column contains non-numeric data and we try to change its data type to Decimal, an error will occur.

To handle these errors, we can use the optional third argument of the Table.TransformColumnTypes function. This argument is a record that contains options for handling data type errors.

For example, if we want to replace any non-numeric values in the “Price” column with null values, we would use the following code:

This code changes the data type of the “Price” column in the “SourceTable” to Decimal and replaces any non-numeric values with null values.

Understanding the M code behind the Table.TransformColumnTypes function

Behind the scenes, the Table.TransformColumnTypes function uses M code to modify the data type of the columns in a table. The M code used by the function is relatively simple and easy to understand.

For example, the following M code changes the data type of the “Price” column in a table to Decimal:

This code first defines the “Source” table, then uses the Table.TransformColumnTypes function to change the data type of the “Price” column to Decimal, and finally returns the modified table.

In this article, we have explored the M code behind the Table.TransformColumnTypes function and learned about some of its features. We have learned how to change the data type of one or more columns in a table, handle data type errors, and understand the M code behind the function.

Overall, the Table.TransformColumnTypes function is a powerful tool for manipulating data types in Power Query, and understanding how it works can help you create more efficient and effective queries.

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

Upcoming Courses

Contact Us