Index Query Step Not Working As Expected

[TomTomTom]

Hello,

I have a step that adds an index starting from an large integer, and then adds on a decimal increment. There are specific reasons to do with uniqueness to use this combination. However, Power Query is not creating the index I am expecting. It is adding extra decimals to the end. Either I have written the index logic incorrectly, or I've missed something about how Power Query createst Indices. It'd be good to know to make sure I've not made a mistake.

The index step is:

Table.AddIndexColumn

                    (

                    DeDeuplication,

                    "Index",

                    Number.Round(LowestAttendanceNo - (Number.From(Date.From(DateTime.LocalNow()))), 0),  

                    0.00001,

                    type number

                    )

I am expecting the index to go up in steps such as 0.00001, 0.00002, 0.00003, etc. Instead it goes up like this:

Any idea where the extra decimal places have come from, and what possible problems / mistakes they reflect ?!

[AlexisOlson · Accepted solution]

I'm pretty sure this is a floating point data type issue.

The largest precision that can be represented in a Decimal Number type is 15 digits long. The decimal separator can occur anywhere in the number. The Decimal Number type corresponds to how Excel stores its numbers. Note that a binary floating-point number can't represent all numbers within its supported range with 100% accuracy. Thus, minor differences in precision might occur when representing certain decimal numbers.

The last digits you see are artifacts of decimal-binary conversion that occur beyond the 15-digit precision limit.

[TomTomTom]

Thanks! Good to know and I think then nothing to worry about in this context.