How to Fix Power Query Showing False for Identical Decimal Values
Question details
The user is experiencing an issue where Power Query incorrectly evaluates identical-looking decimal values as FALSE during a boolean comparison.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Comparing two columns of decimal data imported from external product databases (such as NAV and Business Central).
- Observed behavior
- Power Query returns FALSE for values that appear identical on screen, which is caused by microscopic floating-point precision differences beyond the displayed decimal places (e.g., 1.25 versus 1.250000000001).
Before altering your query, check if the issue is strictly visual by expanding the decimal places in your source data to reveal hidden micro-differences.
Round Both Columns Before Comparison
Use the built-in rounding function in Power Query to eliminate floating-point precision errors before comparing the columns.
Because computers calculate fractions in binary, tiny invisible discrepancies can occur at the 10th or 15th decimal place. By rounding both columns to an identical precision, you standardize the data and allow Power Query to evaluate them accurately.
Select your data table in Excel, go to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.
Select the first decimal column you want to compare. Go to the 'Transform' tab, click on 'Rounding' in the Number Column group, and select 'Round...'.
In the pop-up dialog, enter the desired number of decimal places (for example, 2 or 4) based on your business logic, and click 'OK'.
Select the second decimal column involved in the comparison and repeat the exact same rounding process so both match.
Go to the 'Add Column' tab, click 'Custom Column', and create your equality formula (e.g., `=[Column1] = [Column2]`). The results will now correctly display TRUE for identical rounded values.
Use an Absolute Difference Threshold Formula
Calculate the absolute difference between the two columns and check if it falls within an acceptable minimal tolerance instead of requiring exact equality.
Manage Data Smoothly with WPS Office
Dealing with complex data processing and floating-point precision errors in Excel's Power Query can be frustrating. Try WPS Office, a free, lightweight, and highly compatible alternative for seamless data analysis, formula calculations, and daily spreadsheet tasks.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your device.
- 2. Open Your Data File: Launch WPS Spreadsheet and easily open your existing .xlsx or database export files.
- 3. Compare with Ease: Use built-in functions like =ROUND() and simple logical formulas directly in the grid to verify data equality without setting up complex queries.
Frequently Asked Questions
Why do floating-point precision errors occur in Excel and Power Query?
Computers store fractional numbers in binary format, which cannot always represent base-10 decimal numbers precisely. This limitation creates tiny inaccuracies (like 1.250000000001 instead of exactly 1.25) during data extraction or calculation, leading to FALSE results in equality checks.
Can I fix this by just changing the cell formatting to fewer decimal places?
No. Changing the cell format only alters how the number is displayed visually on your screen. The underlying precision remains intact in the software's memory, meaning Power Query will still evaluate the underlying values as unequal. You must mathematically round the values.
Does Power Query have an exact match setting that ignores small decimals?
Power Query evaluates equality strictly based on the exact stored value. It does not have a native 'fuzzy match' setting specifically for numbers. To ignore small decimals, you must either round the columns first or use a custom tolerance formula.
