Table.TransformColumnTypes in Power Query M (5 Examples)

If you want to change the data type of one or more columns in a table, Table.TransformColumnTypes is the function that does it.

It takes a table and a list of {column name, type} pairs and returns the same table with those columns set to the types you asked for.

Table of Contents

Syntax of Table.TransformColumnTypes Function

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

where

table (required, table). The table whose column types you want to change.
typeTransformations (required, list). A list of {column name, type} pairs, like {{"Units", Int64.Type}}. Pass several pairs to convert several columns in one step, for example {{"Qty", Int64.Type}, {"Price", type number}}.
culture (optional, nullable text). A locale code like "en-GB" or "en-US" for locale-aware parsing of dates and numbers. Omit it to use the current culture. You can also pass a record to set options such as MissingField handling.

Returns: a table with the same rows, where the named columns now carry the requested types. Values that cannot be converted become a cell-level Error rather than failing the whole query.

In plain terms, you tell it which column should be which type, and it hands back the table with those columns retyped.

Example 1: Change a text column to whole numbers

You have a Stock table where the Units column came in as text. The numbers look right, but they are stored as text, and that is what we want to fix.

Here is the starting data:

Item	Units
Hammer	45
Wrench	120
Drill	18

Set the Units column to a whole number type:

let
    Source = Excel.CurrentWorkbook(){[Name="Stock"]}[Content],
    Typed = Table.TransformColumnTypes(Source, {{"Units", Int64.Type}})
in
    Typed

The Units values are now real whole numbers:

Item	Units
Hammer	45
Wrench	120
Drill	18

Int64.Type is the type for whole numbers, so Units can now be summed and compared like a number.

Example 2: Change multiple columns at once

You have an Orders table where both Qty and Price arrived as text. You want Qty as a whole number and Price as a decimal.

Here is the starting data:

Product	Qty	Price
Notebook	3	4.5
Folder	10	2.25
Binder	5	6.75

Pass two {column name, type} pairs in one call:

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Typed = Table.TransformColumnTypes(Source, {{"Qty", Int64.Type}, {"Price", type number}})
in
    Typed

Both columns are retyped in a single step:

Product	Qty	Price
Notebook	3	4.5
Folder	10	2.25
Binder	5	6.75

Qty becomes a whole number and Price becomes a decimal (type number), so each column behaves the way you expect in later steps.

Example 3: Change a number column to text

Sometimes you want the opposite. Here Points is a real number, but you want it stored as text (for example, to keep it as a label).

Here is the starting data:

Player	Points
Asha	88
Diego	95
Lena	72

Set the Points column to type text:

let
    Source = Excel.CurrentWorkbook(){[Name="Scores"]}[Content],
    Typed = Table.TransformColumnTypes(Source, {{"Points", type text}})
in
    Typed

The numbers are now text values:

Player	Points
Asha	88
Diego	95
Lena	72

The Points column now holds text, so it sorts alphabetically and can be joined with other text.

Example 4: Parse a text date with a culture

You have a Shipments table where ShipDate is text in dd/mm/yyyy form. Reading it with the wrong locale would flip the day and month, so you pass "en-GB" to parse it correctly.

Here is the starting data:

Ref	ShipDate
SH-01	15/03/2024
SH-02	07/11/2024
SH-03	28/06/2024

Convert ShipDate to a date using the "en-GB" culture:

let
    Source = Excel.CurrentWorkbook(){[Name="Shipments"]}[Content],
    Typed = Table.TransformColumnTypes(Source, {{"ShipDate", type date}}, "en-GB")
in
    Typed

ShipDate is now a real date, parsed as day/month/year:

Ref	ShipDate
SH-01	15/03/2024
SH-02	07/11/2024
SH-03	28/06/2024

The "en-GB" culture reads 15/03/2024 as 15 March, not as an invalid 3rd day of the 15th month. Use "en-US" when your text dates are in mm/dd/yyyy form.

Example 5: Fix imported text types in a real query

A common real-world case: you import a RawSales table and everything lands as text. You want OrderID as a whole number and Revenue as a decimal so the rest of the query can do math.

Here is the starting data:

Region	OrderID	Revenue
North	1001	2500
South	1002	1800
East	1003	3200
West	1004	950

Retype both numeric columns right after the source step:

let
    Source = Excel.CurrentWorkbook(){[Name="RawSales"]}[Content],
    Typed = Table.TransformColumnTypes(Source, {{"OrderID", Int64.Type}, {"Revenue", type number}})
in
    Typed

The numeric columns are now ready for math:

Region	OrderID	Revenue
North	1001	2500
South	1002	1800
East	1003	3200
West	1004	950

Region stays text, while OrderID and Revenue become numbers you can sum, group, or aggregate downstream.

Things to keep in mind with Table.TransformColumnTypes

Only scalar types work here. Use number, text, type date, Int64.Type, logical, and similar. You cannot set a column to list, record, or table with this function.
A missing column name raises an error. Naming a column that does not exist throws Expression.Error: The column 'X' of the table wasn't found. unless you pass a record with MissingField.Ignore.
This is the step the UI auto-adds. When you click “Detect Data Type” or change a column header’s type icon, the Power Query editor writes a Table.TransformColumnTypes step for you.
It changes types, not values. To change the actual contents of a column (trim, round, uppercase), use Table.TransformColumns instead. To rename a column rather than retype it, use Table.RenameColumns.

Performance and query folding

Against a foldable source like SQL Server, a type change can fold into the source query so the database does the cast.

On Excel, CSV, or folder sources it runs locally, which is fine for typical table sizes.

Common questions about Table.TransformColumnTypes

What is the difference between Table.TransformColumnTypes and Table.TransformColumns?

Table.TransformColumnTypes only changes a column’s data type. Table.TransformColumns changes the actual values in a column, such as rounding numbers or trimming text.

How do I handle a column that fails to convert?

Replace or remove the bad values before the type change, for example with Table.ReplaceValue or by filtering them out. That way every remaining value converts cleanly instead of leaving Error cells.

List of All Power Query Functions

Related Power Query Functions / Articles: