Table.TransformColumnTypes
Sets the data type of one or more columns, such as text, number, date or whole number.
Table.TransformColumnTypes(table as table, typeTransformations as list, optional culture as nullable text) as tableReturns: 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
- table: the table to change. Required.
- typeTransformations: a list of pairs. Each pair is a column name and a type, for example
{"Amount", type number}. Required. - culture: the language and region used to read numbers and dates, such as
"en-US". Optional, but important for dates.
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
- Clean text first, such as removing a dollar sign or a stray "N/A", then convert. Otherwise the conversion fails.
- Set types near the end of the cleaning steps, but before you filter or sum.
- The culture matters. Without it, a date such as 03/04/2026 can be read two ways, depending on the computer's settings.
- Query folding: type conversion on a database source usually folds into a CAST, so it is cheap. Check View Native Query.
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.