'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix Power Query Removes Currency Decimals After Loading Data
Question details
The user needs to prevent Power Query from stripping exact decimal places from currency values when transforming and loading datasets.
- Product
- Microsoft Excel (Power Query)
- Device & OS
- not provided
- Scenario
- Combining multiple files and loading data using the Power Query editor.
- Observed behavior
- Currency values lose their decimal places upon loading, despite the affected columns being formatted as Decimal or Currency.
Verify that the raw source files actually contain decimal values (e.g., open the CSV in Notepad) rather than relying on cell formatting that merely displays whole numbers with trailing zeros.
Review and Update Data Type Conversion Steps
Adjust the 'Changed Type' step in Power Query to ensure currency columns are explicitly set to Decimal or Currency after all other transformations are completed.
Power Query frequently applies an automatic 'Changed Type' step based on the first 1,000 rows of data. If the initial rows lack decimals, the query may incorrectly cast the entire column as a whole number, stripping decimals from subsequent rows.
In Excel, navigate to the 'Data' tab and click 'Get Data', then select 'Launch Power Query Editor'.
Look at the 'Query Settings' pane on the right side of the editor and find the 'Applied Steps' list.
Click the 'X' next to any automatic 'Changed Type' steps that occur before your data combining is finished, or modify them by clicking the gear icon.
Select your currency column, go to the 'Transform' tab, click 'Data Type', and specifically choose 'Currency' or 'Decimal Number'.
Click the 'Home' tab and select 'Close & Load' to apply the query with the exact decimal values preserved.
Apply Data Type Using Locale Settings
Use the 'Using Locale' option to prevent decimal separators from being misread or dropped due to regional format conflicts in combined files.
Process and Format Data Seamlessly with WPS Office
If you struggle with unexpected data type conversions and complex Power Query settings in Excel, consider switching to WPS Office. It provides a lightweight, intuitive spreadsheet environment that accurately handles large datasets, ensuring your numeric formatting and decimals remain exactly as intended.
- 1. Install WPS Office: Download and install WPS Office for free from the official website.
- 2. Import Your Data: Open your CSV or Excel dataset directly in WPS Spreadsheet without running it through complex background queries.
- 3. Format Cells Securely: Select your currency columns, right-click to choose 'Format Cells', and easily lock in your required decimal places.
Frequently Asked Questions
Why does Power Query automatically change my data types?
By default, Power Query profiles the first 1,000 rows of your dataset to guess the appropriate data type. If the sampled currency values are predominantly whole numbers, it may apply an integer data type, causing decimals in the remaining rows to be dropped upon loading.
How can I stop Power Query from automatically detecting data types?
You can disable automatic type detection in Excel by going to File > Options and Settings > Query Options. Under the 'Data Load' section, select 'Never detect column types and headers for unstructured sources'.
Can regional settings cause decimals to disappear in Power Query?
Yes. If your source data uses a comma as a decimal separator (common in Europe) but your system expects a period (common in the US), Power Query may interpret the number incorrectly, either dropping decimals entirely or multiplying the value.
Does setting the column to 'Currency' guarantee two decimal places?
Setting the column to 'Currency' in Power Query ensures it is treated as a monetary value, which natively supports up to four decimal places. However, the final display format with two decimals must still be configured in the destination Excel worksheet.
