How to Fix an IF Function Returning Null in Power Query
Question details
The user needs to calculate the duration between project tasks but receives null values when the completion date is blank or certain milestone dates are missing.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating the number of days between project tasks where job sites might be unfinished, requiring a fallback to the current date or previous milestones.
- Observed behavior
- The Power Query duration calculation returns null instead of a valid number of days when encountering blank date fields.
Ensure all your date columns are properly formatted as 'Date' or 'Date/Time' data types in the Power Query Editor before performing duration calculations.
Use a Cutoff Date Column and Null-Coalescing Operator
This method creates a fallback date for unfinished tasks using the current date, and gracefully handles missing previous milestone dates using the '??' operator.
The null-coalescing operator (??) in Power Query evaluates the left operand. If it is not null, it returns it; otherwise, it evaluates and returns the right operand. This is perfect for stepping back through milestone dates.
Go to the 'Add Column' tab and select 'Custom Column'. Name the column 'CutOffDate' and enter the formula: = if [Completed] = null then DateTime.LocalNow() else [Completed]
Select the newly created 'CutOffDate' column, click the Data Type icon in the column header, and change it to 'Date' or 'Date/Time'.
Create another Custom Column named 'Duration'. Use the null-coalescing operator (??) to fall back to previous milestones if the latest one is missing. For example: = [CutOffDate] - ([#"24-Hour Run Test"] ?? [Rotor Install] ?? [Prework Walkdown])
Use Nested IF Statements for Explicit Logic
If you prefer explicit conditional logic without adding helper columns, you can use nested IF/THEN/ELSE statements to evaluate the dates sequentially.
Try WPS Office for Efficient Data Analysis
While Power Query's M-language is specific to Microsoft Excel, WPS Office offers a free, lightweight, and highly compatible alternative for everyday spreadsheet tasks and data analysis.
Frequently Asked Questions
What does the '??' operator do in Power Query?
It is the null-coalescing operator. It returns the value on its left if it is not null; otherwise, it returns the value on its right. It is extremely useful for providing fallback values when dealing with missing data.
How do I get today's date dynamically in Power Query?
You can use the DateTime.LocalNow() function in M code to get the current system date and time. If you only need the date without the time, wrap it in DateTime.Date(DateTime.LocalNow()).
Why does subtracting two dates in Power Query return an error or null?
This usually happens if one of the fields contains a null value, or if the columns have not been explicitly formatted as 'Date' or 'Date/Time' data types before performing the mathematical operation.
Can WPS Spreadsheet run Microsoft Power Query M-code scripts?
WPS Spreadsheet supports advanced data analysis, pivot tables, and VBA-like macros, but the direct execution of Power Query M-code scripts is a proprietary feature specific to Microsoft Excel.
