Search code examples
exceltextdecimalpowerquerym

Prevent Excel PowerQuery from turning Decimal Numbers into Scientific Notation

I have an Excel sheet with several decimal values. All are 4 decimal places:

0.0299
0.0370
0.0360
0.0478
0.0570

I am loading the sheet (and many others) into a file using PowerQuery. When it reads the source sheet it brings in that column as AlphaNumeric and they are changed to Scientific:

2.989999999999E-2
3.599999999999E-2
4.780000000000E-2
0.0570

Yes, that last one remained the same, I have no idea why. There are a few others that remained the same, but the vast majority of them changed.

If I change the Type to Numeric it goes back to what I want, however I ultimately need the field to be Text. When I change it to text it goes back to lots of decimals (but not scientific)

0.2989999999999
0.3599999999998
0.4780000000002
0.0570

First off, WHY is it doing this?!?! It does not do this with the other 12 sheets I am loading! Secondly, how do I fix it so it either stops doing it in the first place, or to convert it to the text that I need.

Solution

  • This is to do with the way computers use floating points to store decimals. Computers store their data in binary and not all decimal numbers can be represented in binary so an approximation is used. It is the famous 0.1 + 0.2 == 0.3 returning false. This is a very complicated topic but you can watch a video here that explains it.

    https://www.youtube.com/watch?v=PZRI1IfStY0&t=395s

    To fix it, click the column and then round to 4 decimal places and then change the data type to text.

    enter image description here