Time column appearing as datetime on power Query

Power Query
1

Hello,

I have a couple of columns with time: for example 00:03:15, in Excel, however in power BI its appearing as 12/31/1899 12:03:15

It’s the same for the duration column as well. - Any suggestions on how to deal with it ?

Thanks in advance

2

Hello @jps,

Thank You for posting your query onto the Forum.

Well, it’s a typical scenario where while importing the data which is recorded as a “Time” gets converted into “Date and Time” format.

So whenever you load the data into the Power Query this is the type of query generated by default for “Time” related columns. Below is the screenshot provided for the reference -

Default Query For Time
Default Query For Time1546×334 35.7 KB

If you observe the above screenshot I’ve highlighted the part in “Red” color. Just change it from “datetime” to “time”. Below is the screenshot provided for the reference -

Results
Results1539×315 26.8 KB

Once you change it, you’ll see the results as per “Time” format rather than “Date and Time” format.

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Time Data - Harsh.xlsx (8.5 KB)

Time - Harsh.pbix (14.2 KB)

3

Thank you Harsh, for a Prompt Response.
You just pointed out something that I wasn’t looking at. - I was making attempts with changing the data type of the columns.

Ill Try this out and I am sure this will now work.

Cheers!!

4

Hello @jps,

You’re Welcome. :slightly_smiling_face:

And also there’s another work around for this. Let’s say someone is beginner and at a learning stage in Power Query (M Language).

What one can actually do here is, split the column by delimiter choosing the option as “SPACE” and once the column is split one column will be converted into Date and other one will be converted into Time. And then you can retain/remove the required/unnecessary columns.

Hoping you’ll find this alternative useful and helpful as well.

Thanks and Warm Regards,
Harsh

5

Okay, So I had the times in power Query as 00:00:28, Now when I loaded the dataNow in the power BI Desktop it has changed to 12:00:28, This actually is suposed to be the call hold time, i.e. a duration. so its not correct

I intend to figure out a way to convert 00:0028 to minutes (or hours & minutes in another column where I have a duration of call)

What is the best way to approach.

At the moment I am not sure how to tackle 12:00:28, and convert where it should be 28 seconds, and convert it to a decimal value showing minutes

Any advise on this please

6

You know what … I just figured it out . it was right there … selected the column, transform tab and under Duration, hit the Total minutes — That got me exactly what I needed . Now I am able todo calculations such as Average Hold time etc…
:slight_smile:

1 Like
7

Hello @jps, good to know you got exactly what you need :slight_smile: Kindly mark the answer that helped you answer your query to close this thread. Thanks!