2

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.

Improve this question

1 Answer 1

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Still annoying that is only did it for the one sheet, but this worked, thank you!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.