AndreWitteZA's avatar
AndreWitteZA
Regular Visitor
8 years ago
Status:
Needs Info

Google BigQuery - Conversion from string to number failed with value '3e-06.0'

Hi

 

We are getting an error when importing from the billing export in the Google Cloud Platform using the BigQuery connector in PowerBI.

 

We are on the latest version of PowerBI Desktop (Oct 17) which resolves the issue of connecting to customer datasets.

 

This is the error we are getting:

 

DataSource.Error: ODBC: ERROR [HY000] [Microsoft][Support] (50090) Conversion from string to number failed with value '3e-06.0'
Details:
DataSourceKind=GoogleBigQuery
DataSourcePath=GoogleBigQuery
OdbcErrors=Table

 

Can you advise how to resolve this as we dont have access to replace or fix the value prior to the conversion as that is handled by the BigQuery converter.

 

Data Sample:

APIS and Embedding

4 Comments

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    8 years ago

    AndreWitteZA

     

    Is the cost column converted from string to number automatically if you use the BigQuery connector? Can you see the "Change Type" step and remove this step in Query Editor - Advanced Editor?

     

    Best Regards,
    Herbert

  • AndreWitteZA's avatar
    AndreWitteZA
    Regular Visitor
    8 years ago

    v-haibl-msft

     

    The column conversion happens automatically on the BigQuery connector, ie on the first source step in the advanced editor

  • yan's avatar
    yan
    Advocate I
    8 years ago

    AndreWitteZA not sure how you went around that back then but PBI does not handle BQ datatype mapping correctly. By default BigQuery displays float numbers with scientific notation hence the e-.

     

    You could create a view in BQ that formats the cost column to numeric with ROUND(COST, 2). PBI would then autodetect as decimal number and been able load all values.

Completed Ideas