Excel table data with numbers gets rounded off to 4 decimal places in Power Query

Hi 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. 🙂