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

[AndreWitteZA]

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:

[v-haibl-msft]

@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]

@v-haibl-msft

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

[yan]

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