Forum Discussion
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.
Before adding revenue (no errors)After revenue is addedAfter 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
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)
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
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.
Besides, you can retrieve data from Google Analytics api directly to Power BI following the instructions in this article, then create reports.
Regards,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).