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.
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.
Hi v-jingzhang
Thank you so much for your help. Changing the column to General in excel and then pulling it in the report and changing the data type did the trick for me.
Thank you, I had the same problem. Changing to "Time Only" in power query worked for me. Then after I hit "Close and Load" I changed the time format back to "h:mm" in excel and that worked. I was afraid that if power query changed it to a clock time, it would not change back correctly to just a total time when I selected "h:mm" in excel, but thankfully it worked.
