Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Google Analytics Duration in PowerBI

Hi

I've pulled some GA data and selected 'time on page' to pull into the query editor. This automatically gets assigend 'duration' as the data type in the query editor which is correct, however when applyng to the report for build, the values update to decimals and duration data type is not found anymore.

See screenshots below. What would be a solution to getting it back to duration for reporting? I need to be able to present average time on a page.

 

 

 

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Thanks, I ended up splitting the values out as they were 0.00:00:00 so I could work with minutes and seconds indivually by transforming them.

     

    Thanks again for your help

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak 

       

      As per my thread with with Gabriel, for the data type time, this looks good but doesn't allow for the selectable measure average. Do you have any suggestions of how average could be put together via a custom measure?

       

       

      Thanks

  • Gabriel_Walkman's avatar
    Gabriel_Walkman
    Icon for Continued Contributor rankContinued Contributor
    5 years ago

    Hi!

    I tried durations some time ago but could not get them to sum or average in a visual. I had to change the columns back to hours (as decimal) or minutes (as whole numbers).

    Edit: oh yeah, there should be a 'Time' option under 'Data type' in your screenshot. Have a test yourself.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Gabriel_Walkman 

       

      What values did you reproduce for minutes when selecting whole number as the data type? For me, 55 minutes would convert to 0 as a whole number. 

       

      For the data type time, this looks good but doesn't allow for the selectable measure average. Do you have any suggestions of how average could be put together via a custom measure?

       

       

      Thanks

       

      • Gabriel_Walkman's avatar
        Gabriel_Walkman
        Icon for Continued Contributor rankContinued Contributor
        5 years ago

        I converted duration to minutes in power query. I did not have to take seconds into consideration. New custom column =
        Duration.Hours( [duration] ) * 60 + Duration.Minutes( [duration] )