Forum Discussion

sharpedogs's avatar
sharpedogs
Advocate II
6 years ago
Solved

Time Duration - Help I'm lost...

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 🙂  

 

 

UserSession_StartSession_EndDurationApplication
Test User 12/14/2020 8:59 AM2/14/2020 19:10 PM10:11:53 AMAdobe 
Test User 22/13/2020 7:44 AM2/13/2020 18:54 PM11:09:59 AMOffice
Test User 32/12/2020 7:54 AM2/12/2020 17:41 PM9:47:29 AMCitrix
Test user 42/11/2020 15:11 PM2/11/2020 19:01 PM 3:49:51 AMVisio

 

  • vivran22's avatar
    vivran22
    6 years ago

    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

2 Replies

  • vivran22's avatar
    vivran22
    Community Champion
    6 years ago

    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's avatar
    Greg_Deckler
    Community Champion
    6 years ago

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