Power Query Problems

Fix Power Query Showing Fewer Decimal Places with Accounting Format

Phi Hung VoPhi Hung Vo Nov 10, 2026 870 views

Question details

Users notice a loss of decimal precision when importing spreadsheet data formatted with the Accounting format into Power Query.

How to Fix Power Query Showing Fewer Decimal Places with Accounting Format
Product
Excel
Device & OS
not provided
Scenario
Importing financial or numeric spreadsheet data into Power Query for transformation and analysis.
Observed behavior
Power Query displays fewer decimal places than expected, appearing to truncate or round the underlying numeric values of cells that use the Accounting format.
Before you start

Before troubleshooting, verify the actual stored value in your source data by selecting the affected cell and checking the formula bar, as Excel's cell formatting can hide the true numeric precision.

Solution 1Recommended

Verify Source Data and Adjust Power Query Data Types

Change the source cell format to verify the raw data, then ensure Power Query uses the correct decimal data type to prevent unintended rounding.

The Accounting format in Excel is strictly a presentation layer designed to display currency amounts cleanly. It does not control how the data is stored. When Power Query imports this data, it might interpret the format and apply a default precision limit. Adjusting the source format and the Power Query data type resolves this.

1
Change source format to Number

Select the affected cells in your source Excel worksheet, go to the Home tab, and change the format dropdown from 'Accounting' to 'Number'.

2
Reveal full precision

Click the 'Increase Decimal' button in the Number group to reveal the full precision of your stored values before importing.

3
Update Power Query data type

Open the Power Query Editor, click the data type icon on the left side of your column header, and select 'Decimal Number' to maintain full precision.

4
Reapply format on load

Load the transformed data back into Excel, select the final output table, and reapply the Accounting format for presentation.

Verify Source Data and Adjust Power Query Data Types
Format vs. Value: Remember that presentation formats only change how numbers look. Always rely on the formula bar to see the exact numeric value stored in a cell.
Free Microsoft Office alternative

Handle Data Processing Seamlessly with WPS Office

If you frequently encounter complex formatting quirks and data type issues in Microsoft Excel, consider switching to WPS Office. It provides a lightweight, highly compatible spreadsheet tool that makes managing raw data, decimal precision, and accounting formats straightforward without unexpected data truncation.

  1. 1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
  2. 2. Open Your Excel File: Launch WPS Spreadsheets and open your existing .xlsx or .csv files with zero compatibility issues.
  3. 3. Manage Data Freely: Easily set your column formats to Number or Accounting using the familiar top ribbon, preserving your exact decimal precision.
Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Intuitive interface for handling cell formatting and exact numeric precision.Lightweight installation with fast performance for large datasets.Free to use with comprehensive spreadsheet functionalities.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the Accounting format change my decimal places in Excel?

The Accounting format is designed purely for presentation to align currency symbols and decimal points neatly. It does not alter the underlying numeric value stored in the cell, but it often hides extra decimal places by defaulting to two decimal points.

Does Power Query automatically round numbers upon import?

Power Query does not inherently round raw data. However, an automatic 'Changed Type' step might convert a highly precise decimal column into a 'Currency' or 'Whole Number' data type based on source formatting, which causes it to appear rounded.

How do I force Power Query to show exact decimal values?

To show exact decimal values, ensure the column's data type is explicitly set to 'Decimal Number' in the Power Query Editor. Additionally, verify that the external source data hasn't been rounded prior to import.

Can I apply the Accounting format directly inside the Power Query Editor?

No, Power Query focuses entirely on data transformation, cleansing, and structuring. You should apply visual presentation formats like Accounting or Currency in the final Excel worksheet after the query has loaded the data.