Forum Discussion
Time keeps changing to datetime in Power Query
Hi azakir
It seems Power BI will detect the data types in Excel and import data with these data types directly. To get just time values, select this column, select Transform > Time > Time Only.
Apply this change to data model. Then in Power BI Desktop, change the format of this column to "hh:nn:ss".
Date and Time in Excel are stored as numbers. This enables a user to use these dates and time in calculations. So another workaround is to change this column's number format to General or Number in Excel first. This will convert these time values into decimal numbers. Then import data into Power BI. In Power Query Editor, change this column's data type to Time. This will convert them back to Time values.
Hope this helps.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
And the manual change to Time works? Sometimes Excel column data types are persistent when imported to Powerbi. Is this column immediately assigned a datetime without an explicit type assignment in the early query steps?
Most sources are imported as text before powerbi attempts to identify the datatypes. Excel seems to be a bit different. So i suppose it's possible somebody changed the properties in Excel.
HotChilli The manual changes to time keep on throwing the following error if changed to Text:
The original column in excel spreadsheet should be the time spent at site for example, 12:30:36 basically is time spent at site is 12 hours, 30 minutes and 36 seconds. But Power BI treats it as 12:30:36 PM and throws in a date as well. If I change this to Time, it shows 12:30:36 PM which is also incorrect:
not sure if it has to do with properties in excel, possibly could be.
