Google Analytics time duration formatting issue

[cmackay]

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

[v-caliao-msft · Accepted solution]

Hi @cmackay,

What's the data type that column? You can change the data type to Time.

Regards,

Charlie Liao

[tommcmaster]

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.

[Greg_Deckler]

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