Power Query Problems

How to Keep Power Query Error Values as Text in Excel

Ayan MasoodAyan Masood Oct 12, 2026 870 views

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.

How to Keep Power Query Error Values as Text
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.
Before you start

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.

Solution 1Recommended

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.

1
Open Power Query Editor

Select your query from the Excel workbook and click 'Edit', or navigate to Data > Get Data > Launch Power Query Editor.

2
Remove Automatic Type Changes

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.

3
Select the Affected Column

Click on the header of the column containing the alphanumeric values (like '0522-01-01') to highlight it.

4
Change Data Type to Text

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'.

5
Confirm the Step

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.

Change Column Data Type to Text in Power Query
Data Preserved: By setting the type to Text, Power Query will read the exact characters from your source file without attempting to validate them as dates, keeping your original alphanumeric values intact.
Free Microsoft Office alternative

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. 1. Open WPS Spreadsheet: Launch WPS Office and open a new Spreadsheet document.
  2. 2. Import Your Data: Go to the 'Data' tab and select 'Import Data' to load your text or CSV file.
  3. 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.
Highly compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Intuitive Text Import Wizard prevents automatic data conversion errorsLightweight and fast performance for handling large datasetsFree to use with a familiar, user-friendly interface
microsoft office alternative - wps office

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.