Downloads
Power Query MTable: columnsBeginner

Table.TransformColumnTypes

Sets the data type of one or more columns, such as text, number, date or whole number.

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

Returns: The table with the named columns converted to the types you set.

What it does

Table.TransformColumnTypes tells Power Query what kind of value each column holds. A column that looks like numbers but is stored as text cannot be summed, and a date stored as text cannot be sorted by time. Setting the types converts the values, and values that cannot be converted become errors that you can find and fix.

Syntax and arguments

Example

A bank export stores dates as text in month-first format and amounts as text. The culture is set to US English so 03/04/2026 is read as March 4.

let
    Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
    Typed = Table.TransformColumnTypes(
        Source,
        {{"Date", type date}, {"Description", type text}, {"Amount", type number}},
        "en-US"
    )
in
    Typed

After this step, Date can be sorted and filtered by time, and Amount can be summed. Any value that does not convert is shown as an error.

Example: a second, different use

A running balance column in a bank file is a currency amount, which is stored with fixed decimal places.

let
    Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
    Typed = Table.TransformColumnTypes(Source, {{"Balance", Currency.Type}}, "en-US")
in
    Typed

Things to watch

In business terms

Wrong types are the most common cause of a report that totals to zero or sorts dates in the wrong order. Setting every type deliberately, and naming the culture for dates, prevents both problems.

Related functions

All Power Query M functions · View as Markdown · M basics