Rounding in BigQuery

1

Hi,
I'm using EasyMoph with a round(Field, 2) function for my Sales (Ventes in French) field
When I import the .csv file in Google Big Query (automatic way), I could have something like that
TFact_Sales.csv (951.1 KB)
SELECT Ventes*100 FROM cours-bigquery-dauphine.TP_Ventes.TFact_Sales
= 29997.000000000004

When I look at the .csv file, there are only 2 decimal places: 299.97
So can we claim that the issue is coming from Big Query ?

Note:
I saw an Linkedin post talking about this kind of issue with Power Query and Power BI : #powerbi #powerquery | Dmitry Gudkov | 33 comments

Specific rounding topic
2

Hi Michel,

Yes, it looks like a similar problem.
What's the data type of the [Ventes] field in BiqQuery?

3

image
image632×652 38.5 KB

It's FLOAT !

4

Yes, that's the cause of the problem. Even in the official documentation, the FLOAT type is called "An approximate double precision numeric value".

If you can, change it to NUMERIC, or use rounding when querying it:

SELECT round(Ventes*100,2) FROM ...
5

It works perfectly fine with NUMERIC
Thank you, Dmitry

6

You're welcome! :slight_smile: