Forum Discussion
Google Analytics time duration formatting issue
Hi guys,
We are connecting Power BI Desktop to the Google Analytics online service, and are having an issue with the duration fields (e.g. Avg. Time on Page). On Google Analytics this is in the format HH:MM:SS, however when we pull this field back into the BI report it gives a decimalised format.
An example of this is a time of 00:00:18 is showing on the BI report as 0.00431.
We've created a DAX formula to try converting this...
Duration = MOD(MOD('Secure Website Data (2)'[Avg. Time on Page],3600),60)
...however it is coming up with the same decimalised value.
Could anyone please tell us how the 0.00431 value in Power BI relates to the time duration in GA?
Thanks in advance for your help.
Calum
Hi cmackay,
What's the data type that column? You can change the data type to Time.
Regards,
Charlie Liao
3 Replies
See my blog post here:
http://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
Also vote for a true time duration in Power BI Desktop here:
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8814178-field-of-duration-type
https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-data-types/
Duration is the data type used when we first import our data and can view it as a type before we load the data. From there, Duration is converted into a decimal which we need to convert back.
Tom.
Hi cmackay,
What's the data type that column? You can change the data type to Time.
Regards,
Charlie Liao
