Power Query Rounding One Column When I Don't want it to

Question

Source file downloaded from web source is .csv format. When opened in Excel and saved as .xlsx or .xlsm it shows the proper numbers in the given column as 9.63 or 13.71 etc., no matter the file format. The format of the numbers in the column is general from the original download. There are multiple other columns in this download that contain decimal numbers up to two decimal places also formatted as general when they come into Excel. The problem is when running a Power Query to clean up all the data, the one column rounds up 9.63 to 10 and 13.71 to 14 when I only want the actual decimal number. All of the other columns act properly showing 22.21 as that number without me changing them in the source excel file to decimal numbers or currency. I have changed the format of the one column in Excel before running the power query but it sill always rounds up. I have changed the position of the column in the source file to no avail. I attempted to dump the column into Notepad and paste back to try and get rid of any residual formatting, but no matter what it still rounds. There are only 120 rows of data in the whole file. in the column that is rounding, most of the rows are blank, some have 0 and others contain numbers like 9.63 Stuck!

Answer 1

@Scott Late to the party but have had the same issue so posting to help others who might have experienced this issue.

Edit the Power Query that is rounding, then click on the Advanced Editor.

In the Advanced Editor looking at the code check for any values that list Int64.Type

In the image below Int64.Type is highlighted as an example.

Int64.Type will automatically round decimal numbers to whole numbers. Changing "Int64.Type" to "type number" (without the quotes) will allow the Power Query to treat the data as a number again.

Hope this helped.

Answer 2

https://text4funds-my.sharepoint.com/:x:/g/personal/scott_fundelights_com/EcmkbnYC8aJKoj2cmlvlVhMBcVg0Tp6t0fQbE9BX90dgNQ?e=KfhuX8

Column AX "refunded' is the problem one

Answer 3

Thank you for your effort to help! When I saw that my build was not as current as what you were running, it made me hopeful that maybe getting my upgrade done would solve my problem. However, it didn't make a difference on my rounding problem.