How to Fix Power Query Null Date and Logical Value Errors
Question details
The user needs to resolve an error occurring in Power Query when a custom formula evaluates a column containing null values.
- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Writing custom conditional formulas or M code that compares values in nullable columns, such as Termination dates or Compensation amounts.
- Observed behavior
- Power Query returns a data type conversion error stating that a null value cannot be converted to type Logical.
Review your dataset to identify which columns, especially dates and numeric fields used in your logical comparisons, contain empty cells or null values.
Test for Null Values Before Making Logical Comparisons
Modify your custom formula to explicitly check if a value is not null before applying logical operators like greater than or less than.
Power Query uses short-circuit evaluation for the 'and' operator. By testing for nulls first, you prevent the query from attempting to evaluate the actual date or number condition on an empty cell, which avoids the logical conversion error.
In the Power Query Editor, open the Advanced Editor or the Custom Column dialog where your formula is located.
Add a condition to check for nulls at the beginning of your evaluation. For example, change your logic to: [Termination] <> null and [Termination] > #date(2023,3,31).
Expand this same conditional check to any other fields used in comparisons, such as Compensation or additional date columns.
Replace Null Values Before Evaluating
Clean your dataset by replacing null values with zeroes or default placeholder dates before running custom formulas.
Verify and Correct Column Data Types
Ensure that all evaluated columns are explicitly assigned the correct data types to prevent type mismatch and comparison errors.
Handle Complex Data Easily with WPS Office
While Power Query errors in Excel can be complex and time-consuming to troubleshoot, WPS Spreadsheet offers intuitive, built-in tools for data cleaning, conditional formatting, and logical formulas without the steep learning curve. Enjoy a lightweight, highly compatible alternative to Microsoft Excel.
- 1. Download WPS Office: Visit the official WPS website and download the free installer for your operating system.
- 2. Install and Open: Run the setup file and launch WPS Spreadsheet to access your workspace.
- 3. Open Excel Files Directly: Drag and drop your existing .xlsx workbooks into WPS Spreadsheet to continue editing your data seamlessly.
Frequently Asked Questions
Why does Power Query say it cannot convert a null value to Logical?
This happens when your custom formula expects a true or false (logical) result from a greater-than or less-than comparison, but it encounters an empty (null) cell instead. Power Query cannot compare a null value to a standard date or number without explicit handling.
How do I handle null dates in Power Query custom columns?
You can handle null dates by checking if the field is not empty before evaluating it. Use the syntax `[DateColumn] <> null` combined with the `and` operator. This ensures the date comparison only runs if the cell actually contains a date.
Can I replace null values in multiple columns at once in Power Query?
Yes. Select multiple columns by holding down the Ctrl key, right-click on one of the selected column headers, and choose 'Replace Values'. You can then replace 'null' with a specific placeholder value across all the selected columns simultaneously.
