How to Exclude Weekend Dates from an OData Table in Power Query
Question details
The user needs to filter out Saturdays and Sundays from a large OData dataset in Power Query while maintaining a specific date range that includes past, current, and future months.
- Product
- Power Query (Microsoft Excel / Power BI)
- Device & OS
- not provided
- Scenario
- Importing a large dataset via OData and preparing the data for reporting by removing non-working days (weekends) and restricting the timeline to a specific rolling window of months.
- Observed behavior
- The dataset includes weekends by default, and standard date-range filters (like previous and next months) inadvertently exclude the current month's data if not explicitly combined.
Ensure your dataset is successfully loaded into the Power Query Editor and identify the exact name of the column containing your date values (e.g., TimeByDay).
Use M Code to Filter Weekends and Set Date Ranges
Convert your target column to a Date type, apply a weekday filter using Date.DayOfWeek, and correctly combine previous, current, and future month conditions.
Power Query allows you to manipulate dates dynamically using its built-in M code functions. By setting the week to start on Monday, you can easily identify and filter out weekends (days 5 and 6).
Select your date column (e.g., TimeByDay), go to the Home tab, and change the Data Type to 'Date'. Ensure your newly added step references the previous query step properly, such as: Table.TransformColumnTypes(#"Previous Step Name", {{"TimeByDay", type date}}).
Add a custom filter or step to exclude weekends. Use the formula: Date.DayOfWeek([TimeByDay], Day.Monday) < 5. Because Monday is set as day 0, Friday is 4. This automatically removes Saturday (5) and Sunday (6).
To filter the dataset for specific previous, current, and future months, combine the conditions using 'or'. For example: Table.SelectRows(#"Filtered Weekends", each Date.IsInPreviousNMonths([TimeByDay], 3) or Date.IsInCurrentMonth([TimeByDay]) or Date.IsInNextNMonths([TimeByDay], 21)).
Effortlessly Manage Your Spreadsheets with WPS Office
While advanced Power Query M code is specific to Microsoft Excel and Power BI, WPS Office provides a lightweight, highly compatible alternative for everyday data processing. Enjoy robust filtering, dynamic PivotTables, and seamless file compatibility without the heavy subscription costs.
Frequently Asked Questions
Why does my date range filter exclude the current month's data?
Functions like Date.IsInPreviousNMonths and Date.IsInNextNMonths exclusively look at past and future months. To keep the current month, you must explicitly add Date.IsInCurrentMonth([ColumnName]) to your formula using an 'or' condition.
How do I display the name of the day in Power Query?
You can add a custom column and use the Date.DayOfWeekName([ColumnName]) function. This will extract the day of the week as text (e.g., 'Monday', 'Tuesday') for easier reading and grouping.
Why do I get an error when applying my custom weekend filter?
Errors typically occur if the column has not been properly converted to a 'Date' data type before filtering, or if your M code step accidentally references the original 'Source' instead of the immediately preceding applied step.
