Time Duration - Help I'm lost...

[sharpedogs]

Hi, 

The dataset I have reflects the below date/time format, along with the duration. When I input the data into PowerBi all three time columns end up with an AM or PM at the end and the duration doesn't correctly reflect the time between Start and End Session. 

I need a time chart of some sorts that shows peak times thorught the day for Application usage. I'm at a lose with respect to how I update the date/time fileds i have and then how i overaly the duration to show anything of value..

Please help 🙂  

User	Session_Start	Session_End	Duration	Application
Test User 1	2/14/2020 8:59 AM	2/14/2020 19:10 PM	10:11:53 AM	Adobe
Test User 2	2/13/2020 7:44 AM	2/13/2020 18:54 PM	11:09:59 AM	Office
Test User 3	2/12/2020 7:54 AM	2/12/2020 17:41 PM	9:47:29 AM	Citrix
Test user 4	2/11/2020 15:11 PM	2/11/2020 19:01 PM	3:49:51 AM	Visio

[vivran22 · Accepted solution]

Hello @sharpedogs,

In my experience, dealing with time values is quite tricky in Power BI/Query. I follow simple steps in Power Query to convert such values to ease out the calculation (converting it into number format)

For your sample data, I may recommend the following:

Convert the data type of the Duration field as Time:

Then convert it into Decimal Number:

Transform > Standard > Multiply by 24:

This will convert the time value of 10:11:53 as 10.19 hours

Another alternate route is to calculate it from Start & End session columns:

Add a custom column:

Change the type as Duration

Transform > Duration > Total Hours

Hope this will solve the purpose.

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[Greg_Deckler]

Perhaps try switching your Duration column to a Duration data type in Power Query?