PBI doesn't recognize currency from Google Analytics ( DataFormat.Error )

Question

Hi,&nbsp;I have connected to Google Analytics directly through PBI desktop.&nbsp;(leaving some information as xxx)When currency numbers are above 10million, they are formated as xxxE7, which PBI doesnt recognize.&nbsp;Query:#"Added Items" = Cube.Transform(#"xxx", {{Cube.AddAndExpandDimensionColumn, "ga:yearMonth", {"ga:yearMonth"}, {"Month of Year"}}, {Cube.AddMeasureColumn, "Revenue", "ga:transactionRevenue"}}),Error code:DataFormat.Error: We received unexpected data for a column of type 'Currency'.Details:1.xxxE8&nbsp;I have tried changing region settings (current is swe, changed to various regions)Tried&nbsp;Currency.From("ga:transactionRevenue","en-US") for example, then I get the error message "Couldn't convert to number"&nbsp;Can't get this to work. Would really appreciate help!

smithberg · Answer

I used a workaround:&nbsp;Use the revenue per user GA metric instead, which is formatted as a decimal number.Import ga:users as wellMultiply users and revenue per user

anonymous · Answer

Smithberg,
I am not able to reproduce your issue. In your scenario, firstly remove any “Change type” step for the ga:transactionRevenue in APPLIED STEPS. Then right click the ga:transactionRevenue column in Query Editor,&nbsp; select “Change Type-&gt;Using Locale”, change the data type of the column to Decimal Number with English locale.After that, change the Format of the column to Currency under Modeling ribbon in report view of Power BI Desktop.
Regards,Lydia Zhang

smithberg · Answer

Hi Lydia,&nbsp;I have tried to change locale and data types before, unfortunately the error persists.Before adding revenue (no errors)After revenue is addedAfter locale is changedChanging the locale doesn't change anything. Even with different region settings when I import the data!Do you have any other ideas? =)&nbsp;Regards,William

anonymous · Answer

Smithberg,
&nbsp;
Could you please post sample data of the Revenue column? I find a similar thread stating same issue, and Lina uses a different variable as a workaround.Reagrds,Lydia Zhang

smithberg · Answer

I have tried everything they wrote in the thread (see my original post). I have done some further digging in the problem and these are my findings:When values are formatted as number or decimal number in Google Analytics, there is no problem.The problem only occurs when something is formatted as Currency and exceeds 1.0E7.&nbsp;The easiest way around this seems to be to create a different variable in Google Analytics which doesn't use Currency (like Lina did in the other thread).&nbsp;I have tried everything with different regional settings etc but it doesn't work since the Error occurs in the query directly from the Google API (and cannot be changed as far as I can find).&nbsp;The errors therefore remain and if I change the type of the column (even to text), all the correct values in the columns are changed but the Error values remain the same as the errors occured in the query and every subsequent step is after the first query.&nbsp;Possible solutions as I see it are:&nbsp;Using a different connector than the built in GA connectorUsing a different variable from Google Analytics which doesn't use CurrencyGoing down a few granularity levels so the values don't exceed 1.0E7 (but then you probably have a sampling problem on your hands from the google API)

Forum Discussion

PBI doesn't recognize currency from Google Analytics ( DataFormat.Error )

9 Replies

Related content

[DataFormat.Error]

TOTALWTD not recognizing Calendar

GroupBy DataFormat.Error

DataFormat.Error: File contains corrupted data.

TOTALWTD not recognizing Calendar