cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fhebertdesj
Regular Visitor

Rounding Issue with ODBC & SQL Server vs SQL server connector

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

fhebertdesj_2-1660683282529.png


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

fhebertdesj_0-1660683122389.png

 

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

fhebertdesj_1-1660683201499.png

 

Why ODBC connection return false result?  

Frédéric Hébert

 





1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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.

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

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.

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

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.  


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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Kudoed Authors