List.Numbers() with step of 0.1 creates smallest decimal points

[katizabogar]

Hi,
when I create a list in Power Query M by

= List.Numbers(0, 25/0.1+1, 0.1)

I get smallest decimal points for some numbers (e.g. I get 0.30000000000000004 instead of 0.3 and 0,60000000000000009 instead of 0.6, but 0.1, 0.2, 0.4 and 0.5 are correct).
What's the reason and how can I fix it?

[BA_Pete]

Hi @katizabogar ,

Not sure what's causing that as I can't replicate the issue. It works perfectly at my end.

However, you could try something like this if you're still getting the issue:

= List.Transform(
    List.Numbers(0, 25/0.1+1, 0.1),
    each Number.Round(_, 1)
)

Pete

[katizabogar]

The decimal places are not directly visible in the list. Only when I click on e.g. 0.3 I see in the window below that the field actually contains the number 0.300000000000004. Is this not the case with you?
Your solution with the rounding works, thanks.
However, I'm afraid that similar errors are more often the reason why my code doesn't work and I'd like to understand why that is, instead of always just post-processing the output.

[BA_Pete · Accepted solution]

The WHY is due to floating point numbers i.e. how computers calculate base-10 fractions using binary.

Tom Scott explains it better than I ever could here:

https://www.youtube.com/watch?v=PZRI1IfStY0 

The solution is to be aware of it and round it out of your calculations, or use the most granular unit as an integer e.g. pence, not fractions of pounds etc.

Pete