Time keeps changing to datetime in Power Query

[azakir]

Hi Guys. 

Got a weird one here. We have an excel file on web which we ingest to Power BI. I did a report which was working fine till past few days. Apparently there is a "Time at Site" column in excel spreadsheet which shows the total time spent: 

However, when I pull this sheet in Power BI, it keeps changing the field to datetime and even the values are bit out of whack: 

Even in Power Query,: 

Any idea how I can only get just the time. 

P.S. this was working fine till day before yesterday. 

[v-jingzhang · Accepted solution]

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.

[bgriffey]

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.

[azakir]

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. 

[HotChilli]

Maybe you can post a small sample excel file on 3rd party site and put the link here. I'll try and import it and see what happens

[HotChilli]

Ah, so it should really be duration type?

[azakir]

That's correct. Should be duration type. 

[HotChilli]

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.

[azakir]

@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. 

[azakir]

Hi @HotChilli thanks for your reply. 

Had a look in power query and the nothing in there that changes it to datetime. The highlighted one below is something I did manually to change it time: 

[HotChilli]

Look at the steps in power query.  One of them should be called Changed Type or similar.  Look for the column name and edit the type it gets changed to.  Should be time instead of datetime.