Text numbers are being changed by PQ to have many decimal places

[jbaisch]

[FONT=&quot]I am pulling multiple .xlsx files in using PQ. One of the columns in the data files is called "Orig Cust Reference" which is formatted as a text, but looks like a decimal. [/FONT]
[FONT=&quot]500.1903
717.71714
2.5
505.3111[/FONT]
[FONT=&quot]When PQ pulls the data in, it is adding what appear to be additional decimal places. The 717.71714 shows 717.717409999999997 and 505.3111 goes to 505.3111000000001
Stepping through the PQ load, the data type is never changed from text.[/FONT]
[FONT=&quot] Any suggestions on how I might resolve this issue?[/FONT]
[FONT=&quot] Thank you.[/FONT]

 

[Ken Puls]

Hey,

If you go back to the source Workbook, select that cell and increase the number of decimals on the cell, what happens?

My guess would be that the decimals will start to show, and that the source data is actually values not text. When power query pulls in data from Excel, it pulls in the true values, not the formatted values you see.

Correcting this could be complicated depending on whether there are rules that you can leverage. You may need to round the value down specifically in order to keep values as they are, and possibly come up with more complicated methods if the number of decimals varies.

Ultimately, though, I’d suspect your source data isn’t what you’re expecting here...


Sent from my iPhone using Tapatalk