How to Filter Today, Future Dates, and Null Values in Power Query
Question details
The user needs to set up a dynamic Power Query filter that automatically updates daily to capture today's date, a specified range of future dates, and blank (null) values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering a dataset to show current, upcoming, and unscheduled (null) records while automatically excluding past dates.
- Observed behavior
- The user wants the filter to automatically update based on the current system date, but standard filtering hides null values and requires manual date updates.
Ensure your dataset is loaded into the Power Query Editor and that the column you intend to filter is correctly formatted as a Date or Date/Time data type.
Use the Custom Auto Filter with the Dynamic 'Today' Option
Combine the dynamic 'Today' option with future date and null value conditions using the Custom Filter dialog to ensure your query updates automatically.
Power Query features built-in relative date filters that check your system clock during each refresh. By using the 'is today' dynamic button combined with custom 'Or' logic, you can seamlessly include future dates and retain blank records.
In the Power Query Editor, click the drop-down arrow located on the header of your Date column.
Hover over 'Date Filters' in the context menu, then select 'Custom Filter...' at the bottom of the secondary list.
In the Filter Rows dialog, choose the operator 'is in the current day' (or equivalent Today option) from the dropdown to automatically capture the current date.
Click 'Add rule'. Set the logical operator to 'Or', then choose 'is after or equal to' and select 'tomorrow' or enter your desired future date range.
Click 'Add rule' once more. Ensure the operator is set to 'Or', choose 'equals' from the dropdown, and manually type 'null' into the value field. Click OK to apply.
Manage Your Spreadsheet Data Seamlessly with WPS Office
While Power Query is a specific feature within Microsoft Excel, WPS Office provides a highly compatible, free, and lightweight alternative with intuitive advanced data filtering and sorting capabilities for all your daily spreadsheet tasks.
- 1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite for your operating system.
- 2. Install the Software: Run the lightweight installer and follow the on-screen prompts to set up the software.
- 3. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel workbooks to easily filter, manage, and analyze your data.
Frequently Asked Questions
Why are my null (blank) values disappearing when I apply a date filter?
When you apply a standard date filter, Power Query automatically excludes null values because they do not match the date criteria. You must explicitly add an 'Or' condition where the value 'equals null' to keep them in your dataset.
How do I ensure the Power Query filter updates to the current date automatically?
Instead of hardcoding a specific date from the calendar picker, use dynamic filter options like 'Is Today' or 'Is in the current day'. This tells Power Query to evaluate the system clock every time the data is refreshed.
Can I filter for a specific rolling window of days in the future?
Yes. In the Date Filters menu, you can select 'In the next...' and specify the exact number of days, weeks, or months to dynamically capture a precise rolling window of upcoming dates.
