Reply
Frequent Visitor
Posts: 6
Registered: ‎06-01-2017

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

Hi,

 

I have connected to Google Analytics directly through PBI desktop.

 

(leaving some information as xxx)

When currency numbers are above 10million, they are formated as xxxE7, which PBI doesnt recognize.

 

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

 

I have tried changing region settings (current is swe, changed to various regions)

Tried Currency.From("ga:transactionRevenue","en-US") for example, then I get the error message "Couldn't convert to number"

 

Can't get this to work. Would really appreciate help!

Moderator
Posts: 9,944
Registered: ‎03-10-2016

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

[ Edited ]

@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,  select “Change Type->Using Locale”, change the data type of the column to Decimal Number with English locale.

1.PNG2.PNG

After that, change the Format of the column to Currency under Modeling ribbon in report view of Power BI Desktop.


Regards,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 6
Registered: ‎06-01-2017

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

Hi Lydia,

 

I have tried to change locale and data types before, unfortunately the error persists.

1.PNGBefore adding revenue (no errors)2.pngAfter revenue is added3.pngAfter locale is changed

Changing the locale doesn't change anything. Even with different region settings when I import the data!

Do you have any other ideas? =)

 

Regards,

William

Moderator
Posts: 9,944
Registered: ‎03-10-2016

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

@Smithberg,

 

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 6
Registered: ‎06-01-2017

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

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.

 

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

 

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

 

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.

 

Possible solutions as I see it are:

 

Using a different connector than the built in GA connector

Using a different variable from Google Analytics which doesn't use Currency

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

Frequent Visitor
Posts: 6
Registered: ‎06-01-2017

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

To clarify:

my client's GA is setup with Sweden as Region,

Currency is therefore in SEK.

 

The error occurs directly in the query from GA using PBIs built-in connector.

Query:

 

= Cube.Transform(#"Added Items", {{Cube.AddMeasureColumn, "Revenue", "ga:transactionRevenue"}})

 

After this, I cannot do anything to change the format as the Error has already occured.

There is no format change before this query (see pictures in thread)

 

Does not make a difference if I change my regional settings (tried various regions) when I do the query!

 

Hope someone has a solution, would be a great help!

 

/Smithberg

Moderator
Posts: 9,944
Registered: ‎03-10-2016

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

[ Edited ]

@Smithberg,

Have you changed the region settings(Location and Formats) in your computer to Sweden? And please also change the global region setting to Sweden in Power BI Desktop, then re-connect to GA and check if the issue still persists.
3.PNG2.PNG

Besides, you can retrieve data from Google Analytics api directly to Power BI following the instructions in this article, then create reports.


Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 6
Registered: ‎06-01-2017

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

Hi again,

 

I have tried changing regional settings (both on my computer and in PBI). Nothing works.

Additionally I have tried creating a calculated metric in google analytics based on Revenue but as a decimal number instead of currency. Would have been a solution, but I couldnt get the calculated metric to work in the connector.

 

What does work:

Using google sheets to access the data and then importing the data through the web connector. However, my client deems the information too sensitive to use the publish to web function in google sheets.

 

Using the revenue per user metric in combination with the users in the API connector! However, I do lose some aspects as I wanted to include other dimensions such as DeviceCategory, which I cannot do now as Google will sample my data which would be completely unreliable as I'm then combining two sampled fields (thus multiplying the sample degree). This is the solution I'm going for until I can find a more sustainable solution.

 

I have seen the excel connector guide this is about as fiddly as exporting manual reports from google analytics and then loading an entire folder (as I would have do to manual refreshes either way).

Occasional Visitor
Posts: 1
Registered: ‎12-10-2017

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

Hi, 

 

I´m experiencing the same error when importing data from GA to Power BI, has anyone get to the solution of this issue?? 

 

Any help on this trouble or other way to avoid this kind of error with the currency type. 

 

Thank you!

Highlighted
Frequent Visitor
Posts: 6
Registered: ‎06-01-2017

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

I used a workaround:

 

Use the revenue per user GA metric instead, which is formatted as a decimal number.

Import ga:users as well

Multiply users and revenue per user