How to Keep Power Query Error Values as Text in Excel
Question details
The user needs to prevent Power Query from converting alphanumeric values (such as '0522-01-01') into errors caused by failed automatic date parsing.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing or transforming dataset columns containing non-standard or invalid date formats using Power Query.
- Observed behavior
- Power Query automatically attempts to parse the alphanumeric values as dates, fails due to invalid formatting, and replaces the original data with error values or blanks.
Ensure you have access to the original data source file. It is best to change data types in the initial import steps to prevent irreversible data loss in subsequent query transformations.
Change Column Data Type to Text in Power Query
Prevent automatic date conversion by explicitly defining the column's data type as Text immediately after loading the data.
Power Query often attempts to automatically detect and convert column data types based on the first few hundred rows of your dataset. When it encounters numbers formatted with hyphens, it assumes they are dates. If the format does not match a valid calendar date, it throws an error. Overriding this automatic detection resolves the issue.
Select your query from the Excel workbook and click 'Edit', or navigate to Data > Get Data > Launch Power Query Editor.
In the right-hand 'Query Settings' pane, look at the 'Applied Steps' list. If there is an automatic 'Changed Type' step causing the errors, delete it by clicking the 'X' next to it.
Click on the header of the column containing the alphanumeric values (like '0522-01-01') to highlight it.
Go to the 'Home' or 'Transform' tab on the top ribbon, click the 'Data Type' dropdown menu (which may currently say 'Date' or 'Any'), and select 'Text'.
If prompted to replace the current step or add a new one, choose 'Replace current' if you are modifying an existing type change, or 'Add new step' to apply the text format explicitly.

Effortlessly Manage Data Imports with WPS Office
If you struggle with Excel's automatic data conversions and complex Power Query settings, WPS Office Spreadsheet provides an intuitive Text Import Wizard. It gives you full control over column data types right from the start, preventing unwanted date errors while remaining fully compatible with Microsoft Excel files.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new Spreadsheet document.
- 2. Import Your Data: Go to the 'Data' tab and select 'Import Data' to load your text or CSV file.
- 3. Set Column to Text: In the Text Import Wizard, highlight the column with your non-standard dates and choose 'Text' as the Column data format before clicking Finish.

Frequently Asked Questions
Why does Power Query turn my text into errors?
Power Query automatically profiles data upon import. If it detects a pattern resembling a date (like 0522-01-01), it attempts to convert it. If the value isn't a valid date in your system's locale, it results in a data type mismatch error.
Can I recover the original text once it shows as an error in Power Query?
Yes. Errors in Power Query are calculated on the fly. You can recover the original text by deleting the 'Changed Type' step in the 'Applied Steps' pane that caused the error, then manually setting the column type to Text.
How do I stop Power Query from automatically changing data types?
You can disable automatic type detection by going to File > Options and settings > Query Options in the Power Query Editor. Under 'Data Load' for the Current Workbook, select 'Never detect column types and headers for unstructured sources'.
Does changing the type to text affect my ability to filter the data?
You can still filter the data as text (e.g., using 'Contains' or 'Begins With'). However, you won't be able to use chronological date filters (like 'Before' or 'After') on that specific column unless you parse it into standard valid dates.




