Table.TransformColumnTypes - Table Function

Table.TransformColumnTypes is a Power Query M function that returns a table from the input table by applying the transform operation to the columns specified in the parameter typeTransformations and the specified culture. The function returns a table with the specified column types transformed.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Table.TransformColumnTypes(
   table as table,
   typeTransformations as list,
   optional culture as nullable text,
) as table

Argument	Attribute	Description
table		The table whose column data types need transformation.
typeTransformations		A list specifying which columns to transform and their target data types. Each list item is a pair, with the first element being the column name (text) and the second the desired data type (type). Example: { column name, type name }
culture	optional	The culture argument enables the specification of a Culture code (e.g., “nl-NL” or “en-US”) to align transformations with local formatting conventions. If this argument is omitted, functions default to Culture.Current, which reflects the system’s regional settings.

Description

The Table.TransformColumnTypes function is designed to transform the data types of specified columns within a table. This function operates by altering column types to match the defined data types. An optional ‘culture’ parameter can be provided, such as “nl-NL”, to ensure proper conversion of values based on specific local formatting conventions. If a column is specified that does not exist in the table, an error is thrown.

This function is typically invoked through the Power Query interface during the process of changing column types. It is engineered to use various transformation functions for converting values, with the culture parameter being applied to each function. Internally, it uses the following conversion functions to facilitate these transformations:

Data Type	Conversion Function
Decimal number	Decimal.From
Currency	Currency.From
Whole number	Int64.From
Percentage	Percentage.From
Date/Time	DateTime.From
Date	Date.From
Time	Time.From
Date/Time/Zone	DateTimeZone.From
Duration	Duration.From
Text	Text.From
True/False	Logical.From
Binary	Binary.From

When converting a column to text, the function employs the Text.From function. Consequently, the resulting column type is labelled as ‘nullable text’, consistent with the outputs of conversion functions detailed in the table above. When you specify ‘type text’ but receive ‘type nullable text’ as output, this may come as a surprise, but is expected.

Examples

Suppose you have a table like the one below, with three columns of type text.

Table.TransformColumnTypes base table in Power Query M

Basic Example: Converting Column Types

To convert these columns – ‘Date’, ‘Product’, and ‘Sales’ – into types date, text, and number respectively, you can select the different types in the column header. This action generates the following code:

Table.TransformColumnTypes(
  Source,
  {  
    { "Date",    type date   }, 
    { "Product", type text   }, 
    { "Sales",   type number }
  }
)

Table.TransformColumnTypes transforms Data Types of Columns in Power Query M

This operation does two things:

It converts the values in the columns to their designated types.
It assigns the specified data types to the columns.

Inspecting Data Types

To check the data types assigned to each column after transformation, use Type.TableColumn with Value.Type. For example:

Type.TableColumn( Value.Type( ChangedType ), "Date" )    // Output: nullable date
Type.TableColumn( Value.Type( ChangedType ), "Product" ) // Output: nullable text
Type.TableColumn( Value.Type( ChangedType ), "Sales" )   // Output: nullable number

Now that’s odd. Even though we instructed the function to transform the column to date, text and number, they all received the nullable version of this type. How did this happen?

Surprisingly, despite specifying non-nullable types, the columns receive nullable versions. This happens because the transformation relies on functions like Date.From, Text.From and Decimal.From. Since these functions output a nullable type, Table.TransformColumnTypes also does.

Using the Culture Parameter

The culture parameter in the Table.TransformColumnTypes function is a usefulfeature. It enables you to specify the local formatting conventions for data conversion. The choice of formatting can affect the interpretation of data, for example with dates.

Table.TransformColumnTypes dates can differ depending on local formatting

Consider a sequence of dates. Their interpretation might vary based on regional formatting preferences. For example, in one locale, these dates might be read as January 1st to January 12th, 2024. In another, they could be seen as the first day of each month throughout 2024. If the culture parameter is not specified, Power Query defaults to the local machine’s cultural settings.

To ensure conversion happens consistently on each machine you run the query on, you can specify a custom culture parameter. This parameter directs Power Query to respect a particular set of formatting rules. For example:

Table.TransformColumnTypes(Source, {{ "Date", type date }}, "nl-NL" )
Table.TransformColumnTypes(Source, {{ "Date", type date }}, "en-US" )

Table.TransformColumnTypes using a culture parameter in Power Query M

In these expressions, Power Query is instructed to convert text values into dates using either the Dutch (NL) format (dd/mm/yyyy) or the US (EN) format (mm/dd/yyyy). This ensures that the data is consistently and accurately converted regardless of the user’s locale.

You can inspect the example by pasting the below code into the advanced editor:

let
    Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY7BCsMgEER/RTxnRVdtyLWBHgqBQHsLOUgqVCJtMAn5/aqlYE+77Myb2WGgyFEBF8A1reg5WPOIE5nmdKx+KoLg8do5P8chWNMUogSR0NZG1sdFsloXsgJMbG/WzWQYS1gDJvgSdreR6+4mm9v/Ck4gU8LtZaaZtE+3rNmjSk/9/b83iw3k/j6sTyaVg8YP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t, Sales = _t]),
    ChangedType = Table.TransformColumnTypes(Source1,{{"Date", type date}, {"Product", type text}, {"Sales", type number}}),
    AssignedType = Type.TableColumn( Value.Type( ChangedType ), "Date" ),    // Output: nullable date,
    Source2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMTpVgdINcIlWuMyjVB5Zqics1QueaoXAtUriUK19AAlYvqKkMkV8UCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    ChTypeNL = Table.TransformColumnTypes(Source2,{ {"Date", type date}}, "nl-NL" ),
    ChTypeUS = Table.TransformColumnTypes(Source2,{ {"Date", type date}}, "en-US" )
in
    ChTypeUS

Learn more about Table.TransformColumnTypes in the following articles:

Pitfalls with Table.ColumnsOfType
This article shows how Table.ColumnsOfType may return unexpected results. Often Table.TransformColumnTypes has provided different data types than expected and requires a nullable type. » Read more

Other functions related to Table.TransformColumnTypes are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-transformcolumntypes