Power Query Problems

How to Extract Dates from a Mixed Excel Column Using Power Query

Camila Milosovich Sep 29, 2026 868 views

Question details

The user needs to extract only the date values from a mixed column containing text, numbers, dates, and blanks, moving the dates to a new column while leaving the other data unchanged.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Processing a large dataset (over 23,000 rows) where a single column contains mixed data types.
Observed behavior: Dates need to be accurately identified and separated into a new column, with original date entries replaced by null/blanks, without affecting the text or amount values.

Before you start

Ensure your Excel data is formatted as an official Excel Table (press Ctrl+T) before importing it into Power Query to ensure seamless data processing and refreshing.

Solution 1Recommended

Extract Dates Using a Custom Column in Power Query

This method uses Power Query's M language to conditionally identify and extract date values into a new column, which is highly efficient for processing massive datasets.

Power Query is a powerful data preparation tool built into Excel. By using the 'Value.Is' function, we can accurately isolate dates from a column that also contains text, amounts, or blank cells.

Load Data to Power Query

Select your data range in Excel, navigate to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Add a Custom Column

In the Power Query Editor, go to the 'Add Column' tab and select 'Custom Column'.

Enter the Extraction Formula

In the Custom Column dialog box, enter the following formula: if Value.Is([ColumnO], type date) then [ColumnO] else null. Be sure to replace '[ColumnO]' with your actual column name.

Replace Original Dates

To clear the dates from the original column, add another transformation step to conditionally replace the identified date values in your source column with 'null'.

Load Data Back to Excel

Click 'Close & Load' on the Home tab to output the newly structured and separated data into an Excel worksheet.

Extract Dates Using a Custom Column in Power Query

M Language Case Sensitivity: Power Query's M formula language is strictly case-sensitive. Ensure you type 'Value.Is' exactly as shown with the correct capitalization.

Free Microsoft Office alternative

Discover WPS Office for Your Data Processing Needs

While Power Query is a Microsoft-specific feature, WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office. With native support for Excel formats and powerful built-in functions, it is perfectly suited for managing complex spreadsheets and large datasets.

1. Download and Install: Get WPS Office for free from the official website and install it on your device in minutes.
2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx file seamlessly without worrying about formatting loss.
3. Process Your Data: Utilize WPS Spreadsheet's advanced formulas and filtering tools to organize and extract your mixed data effortlessly.

Completely free and lightweight office suiteSeamless compatibility with Microsoft Excel (.xlsx) filesAdvanced array formulas and robust data filtering toolsFamiliar ribbon interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Do I need to know M code to use Power Query in Excel?

While basic data transformations can be done using the Power Query graphical interface without coding, writing custom M code like 'Value.Is()' is often necessary for specific and complex tasks, such as separating mixed data types based on their format.

Can I extract dates from a mixed column without using Power Query?

Yes, you can use built-in worksheet formulas like 'ISNUMBER' combined with 'IF' statements. However, for large datasets exceeding tens of thousands of rows, Power Query provides significantly better performance and stability.

Why did my custom Power Query column return an error instead of dates?

Errors in Power Query often occur due to incorrect syntax or case mismatches. Because the M language is case-sensitive, you must ensure that you type 'Value.Is' and 'type date' exactly as required, and verify that your column names perfectly match the script.

How do I remove the original dates after successfully extracting them?

In Power Query Editor, you can add another transformation step to conditionally replace values in the original column. You can set a rule to replace any value that is identified as a date with a 'null' value.

Excel & Data Problems