How to Change Data Type in Power Query in Power BI

In Power BI's Power Query, you change a column's data type by selecting the column and using the Data Type dropdown in the ribbon or by applying the Table.TransformColumnTypes function in M code. This changes how Power BI reads and processes the data for that column.

Syntax

The main M code syntax to change a column's data type is:

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

Where:

• table is your data table.
• typeTransformations is a list of pairs: each pair has a column name and the new data type.
• culture is optional and controls locale-specific conversions.

m

Table.TransformColumnTypes(Source, {{"ColumnName", type text}})

Example

This example changes the data type of the column Age to Int64.Type and DateOfBirth to Date.Type in a sample table.

m

let
    Source = Table.FromRecords({
        [Name="Alice", Age="30", DateOfBirth="1992-05-01"],
        [Name="Bob", Age="25", DateOfBirth="1997-08-15"]
    }),
    ChangedTypes = Table.TransformColumnTypes(Source, {{"Age", Int64.Type}, {"DateOfBirth", Date.Type}})
in
    ChangedTypes

Output

[
  {"Name": "Alice", "Age": 30, "DateOfBirth": #date(1992, 5, 1)},
  {"Name": "Bob", "Age": 25, "DateOfBirth": #date(1997, 8, 15)}
]

Common Pitfalls

Common mistakes when changing data types in Power Query include:

• Trying to convert text with invalid format to numbers or dates, which causes errors.
• Not applying the change step properly, so the data type does not update.
• Forgetting to select the correct column name or misspelling it in the M code.

Always check your data format before changing types and preview the results.

m

/* Wrong: Trying to convert text 'abc' to number causes error */
let
    Source = Table.FromRecords({[Value="abc"]}),
    ChangedType = Table.TransformColumnTypes(Source, {{"Value", Int64.Type}})
in
    ChangedType

/* Right: Ensure text is numeric before conversion */
let
    Source = Table.FromRecords({[Value="123"]}),
    ChangedType = Table.TransformColumnTypes(Source, {{"Value", Int64.Type}})
in
    ChangedType

Quick Reference

Action	Description	Example
Change data type via UI	Select column, then choose type from Data Type dropdown	Select 'Age' column → choose 'Whole Number'
Change data type via M code	Use Table.TransformColumnTypes with column and type pairs	Table.TransformColumnTypes(Source, {{"Age", Int64.Type}})
Common data types	Use built-in types like Int64.Type, Text.Type, Date.Type	Int64.Type for numbers, Text.Type for text
Handle errors	Check data format before conversion to avoid errors	Clean or filter data before type change

Key Takeaways

Use the Data Type dropdown in Power Query UI or Table.TransformColumnTypes in M code to change data types.

Always verify your data format matches the target type to avoid conversion errors.

Specify the correct column name and data type when using M code.

Changing data types helps Power BI understand and process your data correctly.

Preview changes in Power Query to confirm data types updated as expected.