Power Query Problems

How to Compare Dates and Check for Null in Excel Power Query

John Wilson Oct 21, 2026 870 views

Question details

The user needs to create a custom column in Power Query that evaluates if a date is earlier than today and if another column contains a valid value.

How to Compare Dates and Check for Null in Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating conditional logic in a Power Query Custom Column using dynamic date comparisons and missing value (null) checks.
Observed behavior: Using capitalized "Null" or "Null.Type" results in syntax errors or returns false for all rows because Power Query's M language is strictly case-sensitive.

Before you start

Ensure your target date columns are correctly formatted as Date data types before attempting to compare them, and remember that the M formula language is completely case-sensitive.

Solution 1Recommended

Use Correct M Language Syntax for Dates and Null Values

Apply the correct lowercase "null" keyword and the DateTime.LocalNow() function in your conditional custom column.

In Power Query, the M language requires precise capitalization. Missing values are represented strictly by the lowercase keyword "null". Comparing a cell against "Null" or "Null.Type" will fail or produce an error.

Add a Custom Column

Open the Power Query Editor. Navigate to the "Add Column" tab on the top ribbon, and click on the "Custom Column" button.

Input the Conditional Logic

In the Custom Column formula box, type the conditional statement. For example: if [Column1] < Date.From(DateTime.LocalNow()) and [Column3] <> null then 1 else 0

Customize Field Names

Replace [Column1] and [Column3] with your actual data column names. Use the "<" operator if the date must be strictly before today, or "<=" if today should be included.

Apply and Load

Click "OK" to save your new custom column. Review the preview data to ensure the 1s and 0s are generating correctly, then navigate to the Home tab and click "Close & Load".

Use Correct M Language Syntax for Dates and Null Values

Case Sensitivity Warning: Always use the lowercase word "null" when checking for empty cells. The term "Null.Type" is a data type identifier in the M language, not an actual value, and cannot be used for logical cell comparisons.

Free Microsoft Office alternative

Process Data Efficiently with WPS Office

If writing complex, case-sensitive M language code in Power Query slows down your workflow, consider using WPS Spreadsheet. It offers a lightweight, intuitive interface with built-in data processing features that require no coding background, while retaining high compatibility with standard Office files.

1. Download and Install WPS Office: Visit the official WPS website to download the free, lightweight WPS Office suite for your system.
2. Open Your Data File: Launch WPS Spreadsheet and easily open your existing .xlsx or .csv datasets without any format conversion.
3. Analyze Data Visually: Utilize standard formulas, PivotTables, and built-in filtering tools to manage missing values and date logic visually, skipping the need for advanced query syntax.

Seamlessly compatible with Microsoft Excel (.xlsx, .xls, .csv) formats.Intuitive built-in data filtering, formula support, and formatting tools.No complex coding or strict case-sensitive scripting required for standard data analysis.Lightweight application with exceptionally fast startup and processing times.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Power Query formula return an error when checking for blank cells?

Power Query utilizes the M formula language, which is strictly case-sensitive. If you type "Null", "NULL", or "Null.Type" instead of the exact lowercase "null", the query engine will not recognize it as a missing value and will throw an error.

What is the difference between null and Null.Type in Power Query?

The lowercase "null" is the actual value that represents a missing, blank, or empty cell in your dataset. "Null.Type", on the other hand, is simply a data type classification within the M language architecture, and you cannot evaluate cell contents against it.

How do I dynamically check against today's date in a custom column?

You can generate today's date dynamically by wrapping the current time function in a date extractor, like this: Date.From(DateTime.LocalNow()). This pulls the system's current date so your query updates automatically every time it is refreshed.

Excel & Data Problems