Table.TransformColumnTypes
Table function · By Tahir Nas · Examples run in our simulator · Published · Updated
Converts columns to types, optionally with a culture.
When to use it
Use it right after import (and after appends or expands) to give every column its real type. Pass the source culture when dates or decimals use a different format from your machine. Review the generated Changed Type step, which hard-codes every column name.
Syntax
Table.TransformColumnTypes(table as table, typeTransformations as list, optional culture as nullable text) as tableParameters
- typeTransformations
- A list of {column, type} pairs.
- culture
- Culture used to read text, such as "en-GB".
Returns
The table with converted columns.
Example
Table.TransformColumnTypes(#table({"Day", "Amount"}, {{"05/03/2026", "1.234,5"}}), {{"Amount", type number}}, "de-DE")Result
| Day | Amount |
|---|---|
| 05/03/2026 | 1234.5 |
Example: day-first dates with a culture
en-GB reads 05/03/2026 as 5 March; en-US would read it as May 3.
Date.Month(Table.TransformColumnTypes(#table({"Day"}, {{"05/03/2026"}}), {{"Day", type date}}, "en-GB"){0}[Day])Result
3
Both results are produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M, and checked on every build.
Good to know
- Values that can't be converted become cell errors.
Common mistakes
- Relying on the machine's culture, so the query gives different dates on different PCs.
- Converting codes with leading zeros to numbers.
The matching command in Excel
- Excel for Windows
- Home > Data Type, or right-click > Change Type > Using Locale
- What you see
- The type icon in the header changes, and Using Locale adds the culture to the step.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Data types in Power Query (Microsoft Learn).
Exercises that use Table.TransformColumnTypes
- beginner~10 minFree
Clean and summarize a sales export
Trim messy region names, keep valid completed orders, remove an exact duplicate and total revenue by region.
- beginner~7 minFree
Import a semicolon-separated stock file (hardware)
Read a CSV that uses semicolons, promote headers and set number types.
- beginner~7 minFree
Skip the title rows in a report export (consultancy)
The export starts with a report title and a blank line. Skip them, then promote the real headers.
- beginner~6 minFree
Keep order numbers' leading zeros (kite shop)
Type a CSV so order numbers stay text (0007, not 7) while quantities and prices become numbers.
- intermediate~8 minFree
Convert day-first dates with the right locale (consultancy)
Claim dates are written 05/03/2026 meaning 5 March. Convert them to real dates using the en-GB culture.
- intermediate~7 minFree
Read amounts written with a decimal comma (consultancy)
A German export writes 1.234,56 for one thousand two hundred thirty-four point five six. Convert it with the de-DE culture.
Learn more
- Convert dates and numbers with a locale lesson
- Choose types deliberately: identifiers are text lesson
- Change type using locale: dates and numbers from other countries guide
- Power Query data type pitfalls that quietly change your numbers guide
- Power Query in Excel for Windows, Mac and the web guide
Related functions
Official documentation: Table.TransformColumnTypes on Microsoft Learn.