Table.TransformColumnTypes
Gives columns a type and converts their values to it: text to numbers, dates, logicals and more.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.TransformColumnTypes(table as table, typeTransformations as list, optional culture as nullable text) as table| Parameter | Type | Description |
|---|---|---|
table | table | The table to type. |
typeTransformations | list | One {column, type} or a list of them, such as {{"Date", type date}, {"Amount", type number}}. |
culture | nullable text optional | The culture that reads numbers and dates written as text, such as "nb-NO". Left out, the query's culture: the model's Locale for import, else its own culture, else en-US. The examples here run in en-US. |
Returns
table. The same rows and columns, with converted values and the new types.
Remarks
- A value that can't be converted becomes an error in its cell, not in the whole column: find such rows with
Table.SelectRowsWithErrors, drop them withTable.RemoveRowsWithErrorsor fill them withTable.ReplaceErrorValues. - Converting to
Int64.Typerounds a half to the even number: 2.5 becomes 2, 3.5 becomes 4. - Text written the way another culture writes it (a decimal comma, the day first) needs that culture: "12,50" is 12.5 in nb-NO, and "03/04/2026" is 3 April in en-GB but 4 March in en-US.
- The typed columns take null: their type is the nullable one.
- Changed Type in Transform data is this step.
Examples
Text from a file, typed
Table.TransformColumnTypes(
#table({"Order", "Date", "Amount"}, {{"1", "2026-01-31", "12.50"}, {"2", "2026-02-03", "8"}}),
{{"Order", Int64.Type}, {"Date", type date}, {"Amount", type number}})| Order | Date | Amount |
|---|---|---|
| 1 | 2026-01-31 | 12.5 |
| 2 | 2026-02-03 | 8 |
Norwegian dates and decimals
Table.TransformColumnTypes(#table({"Date", "Amount"}, {{"31.01.2026", "12,50"}}), {{"Date", type date}, {"Amount", type number}}, "nb-NO")| Date | Amount |
|---|---|
| 2026-01-31 | 12.5 |
Whole numbers round halves to even
Table.TransformColumnTypes(#table({"Units"}, {{"2.5"}, {"3.5"}}), {"Units", Int64.Type})| Units |
|---|
| 2 |
| 4 |
Related functions
Table.TransformColumns, Table.Schema, Number.FromText, Date.FromText.