Rounding Issue with ODBC & SQL Server vs SQL server connector

[Anonymous]

With Power query, I read a sql database from a field Numeric(23,7) define and I have around 3.7 millions lines.  All my 3.7 millions have only 2 decimal value ...

If a use ms-sql server connector and read my 3.7 millions and done a sum, return two decimal value:

If I use odbc connection to my sql database my sum return more then two decimal value...

Why ODBC connection return false result?  

Frédéric Hébert

[lbendlin · Accepted solution]

decimal numbers are stored as approximations. so instead of 10.20 you may see it stored as 10.19999999  .  Given enough numbers that difference can add up.

Process your numbers so they round to the nearest hundredth  before summing them.

[marco_fedeli]

Hi

Since I have a very similar issue, I would like to ask you to clarify on this topic. Are you saying that Power BI stores approximations? Why would that be the case, when importing via ODBC but not when importing from SQL?

Moreover, I have noticed that Power BI displays the correct values when importing via ODBC for whole numbers and "divided by two numbers" like .0, .5, .25, .75, .125 and so on. So in this case, there is no approximation happening.

In my opinion, this is a bug in the ODBC connector inside Power BI. Am I wrong about this?

Thank you in advance and best,

Marco

[Anonymous]

Hi Marco, Yes my problem with ODBC and sum is done in Power Query is wrong because return value after two decimal and all my data is two decimal or less.  I don't have this problem if use sql connector.  

[lbendlin]

It's not as much a bug as it is a precision limitation.

Computers store data in bits and bytes. Anything that is not a power of 2 will always be approximated. Eventually the number of bytes used for the storage of a number will define the precision limits. These connectors likely use different numbers of bytes.