Google Analytics Duration in PowerBI

[Anonymous]

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

[Gabriel_Walkman]

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]

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]

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] )

[Anonymous · Accepted solution]

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

[amitchandak]

@Anonymous , you can change data type to time and check

https://community.powerbi.com/t5/Service/Google-Analytics-time-duration-formatting-issue/td-p/107011

[Anonymous]

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