Power Query changes last digits when loading file from folder

[SebastiaanK]

Hi all 

Can anyone of you please help me with the following? I am at a loss for what is causing this and would like your help. 

Problem: 

After loading file from folder using power query, my column with HS codes (22 digit long numerical values), seems to show numbers that are not existing in my source file; causing me to not be able to make a correct innerjoin between two files. 

Details and example

• I have a large excel file 131 MBs that i am loading into power query.
• This file contains ~760k rows and ~150 columns. 
• One of these columns contains a HS product code with 22 digits, from which i need the first 10 digits to make a inner join
• After loading the excel file from folder and expanding the data i see: 

HS Code	rowcount
7318159584999999800000	5809	full
7318159584	5809	10digits

• However when i look at my original source file these values do not at all exist
• Value in source file shows: 

HS Code	rowcount
7318159585000000000000	5809	full
7318159585	5809	10digits

• This issue occurs for approximatly 30% of unique HS codes
• Can someone please explain the cause of the issue/and suggest me a possible solution!? 

Thank you very much in advance. 

what i already tried

• Treat columns as text
• Do not let power query auto interpret the column type
• Clean/trim columns

i expect it might be something related to a variable type used, or power query doing something weird with scientific notation of big numbers, however i am not at all sure? 

Please help

thank you in advance

[SebastiaanK]

Thank you @ronrsnfld @lbendlin very much for the feedback, it seems that it is indeed that. I will need to ensure that the source file treats these columns as text it seems to solve this. The help is much appreciated! 

[ronrsnfld · Accepted solution]

Most likely, the value in Excel is stored as a number. Since Excel has precision limited to 15 digits, and this number has 22 digits, the value actually stored is only an approximation. The actual value stored (which is what PQ will return) may be 7.318159585E+21. 

When this gets detected by PQ, because of the same accuracy requirements and whatever algorithm is used to deal with values that are at the precision limits, you see what you see.

The best "fix" might be to ensure that your source has the HS code stored as text and not as numbers.

I suppose you could try first truncating to 15 digits then divide by 10^5, or something similar. But better would be to fix the data at the source.

[lbendlin]

Do not allow Power Query to change the type of that column. If it comes in as text, keep it as text. If it comes in undefined ("ABC123") leave it like that or convert to text.  Make sure to uncheck the "automatically suggest column types"  option, otherwise it will try to do it behind your back.