Forum Discussion
Excel table data with numbers gets rounded off to 4 decimal places in Power Query
Hi,
I got my data in Excel in which certain columns got numbers with more than 4 decimal places. When I bring this data in Power Query for further process, I noticed numbers with more than 4 decimal places are rounded off to only 4 decimal places in Power Query. Numbers 9.448, 13.4678 from Excel remains as it in Power Query, but number 16.968373456 in Excel is rounded off to 16.9684 when brought into Power Query.
Any idea why and how I can get it fixed?
Thanks,
Hi gafoorgk Check excel data format. If it is correct, then try changing data type to decimal number in power query in the first place after connecting to power query. See image below:
Fixed decimal type has 4 decimal places to its rights. Read this article:
Hope this helps!!
If this solved your problem, please accept it as a solution!!
Best Regards,
Shahariar Hafiz
4 Replies
Hi gafoorgk Check excel data format. If it is correct, then try changing data type to decimal number in power query in the first place after connecting to power query. See image below:
Fixed decimal type has 4 decimal places to its rights. Read this article:
Hope this helps!!
If this solved your problem, please accept it as a solution!!
Best Regards,
Shahariar HafizHi shafiz_p
In Excel the column is formatted as currency. I confirmed the column cells have correct values, some having up to 8 decimal places even though displayed value is rounded off to 2 decimal places. Here is step-by-step actions I did.
- A column is formatted as currency. One of the cell contains a value 133.4665714 though it is displayed in cell as 133.47
- I imported data in PowerQuery (Data tab -> Transform Data section -> From Table/Range)
- The Source table of imported query in PowerQuery has the above value rounded off as 133.4666.
- I tried changing data type of this column as Decimal Number (just like the way you did show in screenshot). But result is same since the first step of the query (Source) itself has only 4 decimal places.
- Even tried right clicking the column and Transform -> Round -> Round To and giving value of 10. Not working.
Now, when you asked me to check the data type in Excel itself, I did something as following;
- Previously the column of first formatted as currency and then applied a custom format text as I needed the column to be displayed.
- Now, I cleared the fomatting completely, applied Accounting format first and then applied the same custom format text as done previously.
- This time when exported to PowerQuery, it gave the entire decimal places there.
So, it could be a bug. The reason why I call it a bug is, if importing table data in PowerQuery is also doing an automatic TransformColumnTypes method, why does it need to round off numbers based on initial formatting applied in Excel?!
Anyway, thanks for your input. 🙂
Thanks for this GA
I encountered the same issue, with my original excel file having custom formatted to currency, and excel power query rounding i to 4 decimals as part of the import process, despite having more available. Used your fix to remove the formatting chaging it to accounting and it worked fine.
Cheers!
I already tried everything in Power Query including what you suggested, but didn't work. The imported query Source step itself has number rounded off to 4 decimal places.
But your suggestion to check formatting in Excel made me do something, which worked. Number column in my case was initally formatted as Currency and then applied a custom formatting. Export this to Power Query and you get this number column with values rounded off to 4 decimal places. Now, clear the formatting from same column in Excel, format it as Accounting, apply the same custom formatting and then export it into Power Query. There you'll see the number value exactly as in Excel. Strange!!
It most probably is a bug, can't be by design. Since your suggestion made me work on a solution, I thank you and accept your reply as solution.
