How to Fix Power Query Merge Mismatches Caused by Decimal Precision
Question details
Users encounter incomplete merges in Power Query because identical-looking numerical values fail to match due to hidden floating-point decimal precision.
- Product
- Power Query
- Device & OS
- not provided
- Scenario
- Merging two queries based on a numerical column where displayed values appear identical, but the internal floating-point values differ.
- Observed behavior
- Power Query treats functionally identical values (like 2764.72 and 2764.72000000003) as entirely different records, causing data rows to drop or return null during the merge operation.
Identify the specific numerical columns acting as the matching keys in both queries, and determine the exact number of decimal places necessary for your analysis before attempting the merge.
Round Values to a Specific Precision Before Merging
Prevent floating-point mismatches by explicitly rounding the numerical values in both tables to the exact same decimal places.
Because data engines sometimes store simple decimals in binary format, microscopic differences can occur (e.g., storing 2764.72 as 2764.72000000003). By proactively rounding the column in Power Query, you force the engine to evaluate the exact intended value.
Open the Power Query Editor and click on the header of the numerical column (e.g., Compensation) that you plan to use for the merge.
Navigate to the Transform tab on the top ribbon, click on the Rounding dropdown, and select 'Round...'.
In the pop-up dialog, enter the desired number of decimal places (such as 2) and click OK. Repeat this exact process for the matching column in your second query before executing the merge.
Change Data Type to Fixed Decimal Number
Applying a fixed decimal data type automatically standardizes the precision, ignoring microscopic floating-point variations during a merge.
Create a Rounded Custom Helper Column
Add a new column specifically for merging without permanently altering the precision of your original raw data.
Handle Complex Data Merges Seamlessly with WPS Office
Struggling with complicated Power Query errors and floating-point mismatches in Microsoft Office? WPS Office provides a lightweight, highly compatible, and user-friendly spreadsheet environment. Effortlessly merge, match, and analyze your data using robust built-in functions without the heavy processing overhead.
Frequently Asked Questions
Why do floating-point errors occur in Power Query?
Data engines convert and store decimal numbers in binary format. Because some decimals cannot be represented perfectly in binary, microscopic precision differences (like .00000000003) are generated. These hidden differences cause exact matching operations to fail.
Does changing the visual display format fix the merge mismatch?
No, changing the display format only changes how the data looks on your screen. Power Query evaluates the underlying stored value during a merge, which is why explicit rounding or changing the actual data type is necessary to resolve mismatches.
Is using Fixed Decimal better than Rounding the column?
It depends on your data. Fixed Decimal is faster to apply and ideal for financial data because it automatically caps at four decimal places. Rounding gives you exact control over the specific number of decimal places needed for your unique scenario.
