Uint64 lost of precision

[a_lajoie]

Our Id are 64 bits unsigned integer Relation seems broken due to lost of precision on the int 7133283991207464807 seems to be tracked as 7133283991207460000

[james14]

This still seems to be an issue as of May 2024.

This "feature" is hinted at in MS documentation but that relates specifically to the display of Int64 values in Power BI visuals. I believe it also affects some Power Query Table.Join and Table.NestedJoin operations - at least where these are not folded back to source databases.

I suspect this stems from that fact that Int64.Type is still basically type number in Power Query, just with facets that indicate that it is semantically an integer. Since type number has to support floating point, and the internal implementation is presumably an IEEE double precision floating point number, we end up with only 53 bits of usable integer precision. (Maybe the Power Query library type ought to have been named Int53).

As for resolution - To avoid breaking existing code, maybe the internal calculations should use IEEE quadruple precision instead of double precision. However, I agree with the original poster from 2016 that this is particularly an issue with IDs used for relationships. In this application, the numeric significance of the value is less important than its uniqueness and ability to be compared for equality. Maybe a PQ library type based on binary type with facets, rather than number type, would be more useful? (It would probably also be useful to add e.g. some hashing functions to the library to return values of this type.)