How to Preserve Two-Digit Decimal Parts and Trailing Zeros in Power Query
Question details
The user needs to isolate and preserve the exact two-digit decimal portion of a number, including trailing zeros, when importing data into Power Query.
- Product
- Power Query
- Device & OS
- not provided
- Scenario
- Importing numerical data that contains a comma as a decimal separator, where maintaining trailing zeros is necessary for separating integer and two-digit decimal parts.
- Observed behavior
- Power Query imports the underlying value instead of the formatted text, discarding trailing zeros. As a result, values like 0,50 and 2,50 are displayed as 0.5 and 2.5, hiding the required zero.
Verify the original data type of your column in the source file, as Power Query imports the underlying mathematical value rather than the visual formatting applied in your spreadsheet software.
Use Mathematical Operations to Isolate Decimals
Multiply the value by 100 and use integer division alongside modulo operations to perfectly isolate the whole number and preserve the two-digit decimal portion.
Because Power Query automatically drops trailing zeros based on mathematical rules, relying on text conversion can sometimes cause errors. Instead, perform simple arithmetic on the dataset to extract the exact two-digit decimal parts without losing the trailing zero.
Ensure your column is using a locale-aware conversion so the comma decimal separator is correctly recognized as a decimal number, rather than text.
Add a Custom Column and multiply your original decimal value by 100. This shifts the two-digit decimal into a whole number (e.g., 2,50 becomes 250).
Change the data type of the newly created column to a Whole Number (Integer).
Use Integer Division (Divide by 100) on the multiplied value to extract the integer part of your original number.
Apply a Modulo 100 operation to the multiplied value. This will yield the exact two-digit remainder (e.g., 50), perfectly preserving the trailing zero.
Convert Result to Text and Split
Change the number to a text string, split it at the decimal separator, and append any missing zeros.
Simplify Your Spreadsheet Tasks with WPS Office
If advanced data processing engines prove too complex for standard decimal formatting tasks, WPS Office offers a free, lightweight alternative with seamless Microsoft Excel format compatibility and intuitive data handling features.
- 1. Download WPS Office: Download and install the free WPS Office suite from the official website.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing data file.
- 3. Format Cells Directly: Select the cells containing your decimal numbers, right-click, choose 'Format Cells', and set the Decimal places to 2 to instantly preserve trailing zeros visually.
Frequently Asked Questions
Why does Power Query drop trailing zeros from my decimals?
Power Query imports the underlying mathematical value of a cell, ignoring the visual display format applied in the spreadsheet. Mathematically, 2.50 is identical to 2.5, so the trailing zero is discarded upon import.
Can I use rounding functions to fix the trailing zero issue in Power Query?
No. Rounding a decimal like 0.50 to two decimal places will still output 0.5 because trailing zeros do not hold mathematical value. Additionally, Power Query uses a round-half-to-even behavior (Banker's Rounding), which can complicate halfway value processing.
How do locale settings affect decimal imports in Power Query?
Locales determine whether a comma or a period is recognized as a valid decimal separator. If your source data uses commas (like 2,50), you must ensure your Power Query locale settings are correctly configured during import so it is recognized as a decimal number rather than a text string.
