Forum Discussion
Power Query Dropping Decimals with Accounting Format
I'm encountering what seems like a bug in Power Query. I have a table with data that contains numbers with more than 4 decimal places. When this data is in the Accounting format, Power Query is only picking up 4 decimal places, even if the Query is formatting the data is Decimal Number (Changed Type).
When the data is formatted as Number, Power Query is able to pick up all decimal places (Some numbers have 10+ decimals)
Is this normal behavior? I would like to use the Accounting format because it looks cleaner, but obviously I cannot sacrifice data accuracy. It is also very illogical to have a format labeled "Accounting" only hold 4 decimal places when passed through Power Query
I did try restarting Excel multiple times, refreshing queries, the only thing that resolved it was changing the local format to Number.
The "Accounting" label is purely for presentation, not data storage. It's designed to cleanly display currency amounts—not preserve mathematical precision.
Try this?Set the cell format in Excel to "Number" (not "Accounting") before importing.
If using Power Query from external sources, make sure you're not importing from an Excel range that’s pre-formatted with Accounting.
You can reapply Accounting format later after the data is loaded and transformed—only in the final presentation layer (Excel, Power BI, etc.)
Please read my entire post. I am aware of what you are saying.
I can't reproduce that
On which Excel platform/version you are?
I am on version 2503, build 18623.20178 64 bit
Edit: even after creating a blank workbook file and creating a new query, PQ still only picks up 4 decimal places when data is in the accounting format, and PQ is attempting to change the data type to Decimal Number
Do you work with .xls or .xlsx files? For them are different behaviour. If .xlsx I have no idea why so.
The Accounting format in Excel is primarily a display format and affects how the data appears rather than how it is stored or calculated. When Power Query encounters data formatted as Accounting, it may revert to a default decimal precision that often ends up being limited.
That is what's happening, but it doesn't make sense. If it is an intended feature, it's an incredibly poor choice on Microsoft's part.
If the source is xml-based file (.xlsx, .xlsm) Power Query takes data from xml structure and grid format shall not affect the result. If the source is binary file (.xls) Power Query takes values from the grid as texts, thus as many digits as format displays.
What's wrong in your case it's hard to say without playing with files. In any case that's not default behaviour if you work with xml-based sources. I'd try to recreate Power Query from scratch to check how it works.
When you change the type to Decimal Number, you’re right in thinking that should ideally allow for more decimal places. However, when you’re dealing with cells formatted as Accounting, Power Query might still regard them as having the limitations of that format.
It seems like that is the case. It is possible I've missed this happening previously, as I only caught it when implementing a check on data accuracy without rounding, but it does seem problematic for business applications that rely on data accuracy, such as my role. To me, this seems like more of a bug than intended feature.
