Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
TomTomTom
Helper II
Helper II

Index Query Step Not Working As Expected

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:
TomTomTom_0-1671630466468.png
TomTomTom_2-1671630503879.png

 

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


 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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.

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

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.

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors