Power Query Problems

How to Create a Power Query Custom Column for Overdue Training Dates

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to create a custom column in Power Query that checks if the dates in two different columns fall within the last two years, outputting a specific overdue flag if neither condition is met.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking employee training compliance by evaluating multiple date columns to identify records that are overdue based on a rolling two-year validity period.
Observed behavior: Requires the correct Power Query M syntax to evaluate two date columns against a dynamic date condition, returning a 'Y' text value when both checks fail.

Before you start

Ensure your source data columns are correctly formatted as 'Date' or 'Whole Number' (for years) in the Power Query Editor to prevent type mismatch errors during conditional evaluation.

Solution 1Recommended

Use a Power Query M Conditional Expression

Apply a complete if/then/else statement in the Custom Column dialog using M code to evaluate your logical date conditions.

Power Query uses the M formula language, which requires strict adherence to its syntax rules. Unlike standard Excel formulas, M code is entirely case-sensitive. This means logical operators like 'if', 'then', 'else', and 'or' must be written in lowercase letters.

Open the Custom Column Dialog

In the Power Query Editor, navigate to the 'Add Column' tab on the top ribbon and click on the 'Custom Column' button.

Name the New Column

In the Custom Column window, enter a descriptive name such as 'Overdue Status' in the 'New column name' field.

Input the M Code Syntax

In the custom column formula box, enter the strict M code syntax: if [ColumnA] >= condition or [ColumnB] >= condition then null else "Y". Replace 'condition' with your specific date threshold (e.g., a specific date value or a dynamic M date function).

Apply and Verify Data Type

Click 'OK' to generate the column. Once created, click the data type icon in the column header and ensure it is set to 'Text'.

Case Sensitivity in Power Query: Always use lowercase for 'if', 'then', 'else', and 'or'. Using uppercase letters (like 'IF') will result in a syntax error.

Free Microsoft Office alternative

Process Data Effortlessly with WPS Office

While Power Query is a specific component of Microsoft Excel, WPS Spreadsheet offers a powerful, lightweight, and completely free alternative for daily data processing. You can manage complex datasets, track overdue dates using robust built-in formulas, and enjoy seamless compatibility without heavy system requirements.

1. Open Your Data in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the training records.
2. Use Standard Excel Formulas: Insert a new column next to your data. Unlike Power Query M code, you can use standard, familiar spreadsheet formulas.
3. Apply the Logic: Enter a formula like =IF(OR(YEAR(TODAY())-A2<=2, YEAR(TODAY())-B2<=2), "", "Y") to instantly flag overdue records without needing to learn M code syntax.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv)Comprehensive built-in logical and date formula library (IF, AND, OR, DATEDIF)Lightweight installation with incredibly fast loading times for large datasetsFamiliar, intuitive user interface ensuring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a syntax error when using 'IF' in Power Query?

The Power Query M language is strictly case-sensitive. You must write 'if', 'then', and 'else' in completely lowercase letters. Using uppercase 'IF' (as you would in standard Excel formulas) will trigger a syntax error.

How do I dynamically calculate the date from two years ago in Power Query?

You can use the M function Date.AddYears(DateTime.Date(DateTime.LocalNow()), -2) in your condition to dynamically return the date exactly two years prior to the current system date.

Can I check multiple conditions in one Power Query custom column?

Yes. You can chain multiple logical checks using the lowercase 'and' as well as 'or' operators within a single 'if...then...else' statement.

What should I replace 'condition' with if my column contains only years?

If your columns contain whole numbers representing years (e.g., 2022), you can replace 'condition' with Date.Year(DateTime.LocalNow()) - 2 to evaluate against the current year minus two.

Excel & Data Problems