Skip to main content

How to Change a Column's Data Type in Power Query

· 4 min read
Jagdish Kumawat
Founder @ Dewiride

Every column in Power Query has a data type, shown by the icon in its header. A SharePoint date column arrives as Date/Time, for example, with a time of midnight you don't need. Changing it to Date keeps only the day. Here's how.

Microsoft's Data types in Power Query lists the types, from Decimal number and Whole number to Date, Date/Time and Text, and says the icon on the left of a column header shows its type. The example is the ExpenseDate column of a query of an Expenses list (How to Get Data from a SharePoint List in Power BI in the Browser), which showed values such as 02/11/2026, 00:00:00.

Step 1: Open the Type Menu​

Select the type icon at the left of the column's header. For ExpenseDate, it's a calendar with a clock, Date/Time. A menu lists the data types; select Date:

The ExpenseDate column header with the Date/Time icon and its menu of types: Decimal number, Currency, Whole number, Percentage, Date/Time, Date, outlined in red, Time, Date/Time/Zone, Duration, Text, True/False, Binary and Using locale...

Step 2: Check the Result​

The header's icon becomes a calendar, and the values lose their time: 02/11/2026. Applied steps gains a step, and the formula bar shows it:

Changed column type
Table.TransformColumnTypes(#"Removed columns", {{"ExpenseDate", type date}})
  • #"Removed columns" is the step before.
  • {{"ExpenseDate", type date}} pairs each column with its new type. Change several columns at once and they all go into this list.

The Expenses query with ExpenseDate showing 02/11/2026, 03/11/2026, 04/11/2026, 06/11/2026 and 10/11/2026, a Table.TransformColumnTypes formula that makes ExpenseDate a date, and the new step outlined in red under Applied steps

The dates show day first because the browser's language in the example was English (United Kingdom).

Common Mistakes​

MistakeWhat happensDo this instead
Leaving dates as Date/TimeReports show 00:00:00 and group by date and timeDate for dates without a time
Changing a column of names to Whole numberEvery value becomes an errorChoose the type that matches the values
Changing a type before removing bad rowsValues that can't convert become errorsFilter first, then change the type

Troubleshooting​

SymptomLikely causeFix
Cells show Error after the changeA value can't be converted to the typeCheck the values, or choose another type
Dates come out with the wrong monthThe text was written in another region's formatUsing locale... with the source's region
The time is still thereThe type is Date/TimeChoose Date

FAQ​

What's Using locale for?​

It converts text using a region's format, for example 02/11/2026 as 2 November in the UK or February 11 in the US. Microsoft's data types article has a section on locale.

Can I change the type in the model instead?​

Yes, the model has its own data type setting. Changing it in Power Query keeps the data clean before it reaches the model.

Conclusion​

Select the type icon in the column's header and choose the type. Power Query adds a Table.TransformColumnTypes step; the example turned ExpenseDate from Date/Time into Date.

Additional Resources​