Power Query Problems

Fix Excel Power Query Changing Decimal Values to Whole Numbers

Khadija Khan Sep 30, 2026 868 views

Question details

The user needs to prevent Power Query from truncating decimal values and incorrectly displaying them as whole numbers (e.g., 16.01 appearing as 16.00) in an Excel PivotTable.

How to Fix Power Query Changing Decimal Values to Whole Numbers in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Loading a dataset with decimal numbers into an Excel PivotTable using Power Query.
Observed behavior: Even after changing the data type to Decimal Number, the output still produces rounded whole numbers with zeroes in the decimal places.

Before you start

Verify your original data source (like the raw CSV or database file) to ensure the decimal points actually exist and were not rounded before entering Power Query.

Solution 1Recommended

Remove Earlier 'Whole Number' Conversion Steps in Power Query

Use this solution if your decimals are converting to whole numbers with .00 at the end. Power Query processes data sequentially, so an earlier step may be rounding your data before your final decimal conversion.

Power Query often adds automatic 'Changed Type' steps when loading data. If an early step converts the column to a Whole Number, the decimal portion is permanently dropped. Adding a subsequent step to change it back to a Decimal Number will only add zeros (e.g., changing 16 to 16.00), rather than restoring the original 16.01.

Open Power Query Editor

In Excel, go to the 'Data' tab and click on 'Get Data', then select 'Launch Power Query Editor' to open your query.

Review the Applied Steps

On the right side of the screen, locate the 'Query Settings' pane and look at the 'Applied Steps' list.

Delete Incorrect Type Changes

Click through each step starting from the top. Look for a 'Changed Type' step that sets your specific column to a Whole Number. Click the 'X' next to that step to delete it.

Set Type to Decimal Number

Select your column, navigate to the 'Transform' tab, click 'Data Type', and choose 'Decimal Number'. Click 'Close & Load' to update your Excel workbook.

Remove Earlier 'Whole Number' Conversion Steps in Power Query

Preserving Data Accuracy: Removing the premature whole number conversion step ensures the decimal data flows through your query unaltered.

Solution 2

Adjust the PivotTable Field Number Format

Apply this solution if the underlying Power Query data correctly contains decimals, but the values are visually rounded to whole numbers inside your PivotTable.

Free Microsoft Office alternative

Switch to WPS Office for Easier Data Management

If troubleshooting complex Power Query formatting and hidden conversion steps in Microsoft Excel is slowing you down, consider switching to WPS Office. It provides a lightweight, highly compatible spreadsheet environment where handling large datasets and creating PivotTables is straightforward and transparent.

1. Download and Install: Get WPS Office from the official website and install it on your PC or Mac.
2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx file directly without any data loss.
3. Analyze Data Easily: Use the highly intuitive interface to format your decimals and generate PivotTables quickly.

100% compatible with Microsoft Excel (.xlsx) formatsLightweight architecture that runs smoothly on older devicesIntuitive PivotTable creation without hidden rounding errorsFree to use for everyday data analysis and formatting

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel automatically round my numbers in Power Query?

Excel's Power Query attempts to automatically detect data types when loading files. If it misinterprets a column as integers based on the first few rows, it will apply a 'Whole Number' type conversion, truncating any subsequent decimal values.

How can I stop Power Query from automatically changing data types?

You can disable automatic type detection by going to File > Options and settings > Query Options. Under 'Data Load', select 'Never detect column types and headers for unstructured sources'.

Can formatting the cell in standard Excel fix the Power Query rounding issue?

No. If the decimal data is lost during a Power Query step, standard Excel cell formatting will only add trailing zeros (e.g., 16 becomes 16.00). You must fix the query steps to restore the original decimal values.

What is the difference between Decimal Number and Fixed Decimal Number in Power Query?

A Decimal Number allows for varying decimal lengths and is a floating-point type, while a Fixed Decimal Number is restricted to four decimal places. Fixed Decimal is highly accurate and is generally recommended for financial and currency data to avoid floating-point errors.

Excel & Data Problems