Forum Discussion

Smithberg's avatar
Smithberg
Regular Visitor
9 years ago

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 does...
Anonymous's avatar
Anonymous
Not applicable
9 years ago

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.



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


Regards,
Lydia Zhang

  • Smithberg's avatar
    Smithberg
    Regular Visitor
    9 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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

      • Smithberg's avatar
        Smithberg
        Regular Visitor
        9 years ago

        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)