Strange behavior on big integers

[svzessen]

Hello,

I have the following situation. One table is directly from the datasource (Snowflake). The other table is via a dataflow from the same datasource. When I join these tables the number will be rounded some way. (See my screendump.)

The number you see on the left bottom is the correct one. When I filter on a number which is different but rounded on 15 digits the same I get always the same number back.

If I join these tables there are more numbers which are joined becauses of the rounding to 15 digits.

I hope someone can tell me what is going on.

[Vijay_A_Verma · Accepted solution]

Reference - https://docs.microsoft.com/en-us/power-query/data-types

Decimal numbers support only 15 digits, hence these are getting truncated to 15 digits. Whole numbers support 19 digits. Your bottom number is 19 digits. So, before merging, you should convert the column to Whole number and then merge. 

Limitation - This approach would work as long as numbers are less than or equal to 19 digits.

For number of any length - You will have to convert the relevant columns in both tables to text. Text will support more than 268million unicode characters. 

Then you can merge these tables on text columns which contains these numbers.