How to Filter Weekdays Using Queries in Microsoft Power BI

How do you filter weekdays using queries in Microsoft Power BI? You filter weekdays using queries in Microsoft Power BI by adding a custom column in Power Query that extracts the day of the week from your date field, then filtering the data to exclude or include specific days, allowing precise control over time-based analysis such as business-day reporting or operational dashboards.

For enterprise executives, filtering by weekdays enables more accurate insights by excluding weekends or focusing on specific days that align with business cycles, improving the quality and relevance of decision-making.

Step 1: Open Power Query Editor

Begin by opening your dataset in Power BI Desktop:

1. Click on Transform data in the Home ribbon
2. The Power Query Editor window will open
3. Select the query containing your date field

Tip: Always work with a clean date field in date or datetime format to ensure accurate transformations.

Step 2: Add a Column for Day of Week

Next, extract the day of the week from your date column:

1. Select your date column (e.g., OrderDate)
2. Go to the Add Column tab
3. Click Date > Day > Day of Week
   • This will add a new column with numbers 0–6 representing days (0 = Sunday, 6 = Saturday)

Optional: Use “Day of Week Name” Instead

• Click Date > Day > Name of Day
• This creates a column with readable names like “Monday,” “Tuesday,” etc.

Pro Tip: Numeric weekdays are useful for performance, while names improve readability during filtering.

Step 3: Filter Out or Include Specific Weekdays

You can now apply a filter based on the new column.

To Exclude Weekends:

1. Click the dropdown on the “Day of Week” column
2. Uncheck 0 (Sunday) and 6 (Saturday)
3. Click OK

This will retain only Monday through Friday in your data.

To Filter for Specific Days:

• Use the day name column and filter for values like “Monday” or “Wednesday” as needed.

Use Case: Filter for weekdays only when analyzing business KPIs like sales, call center activity, or operations performance.

Step 4: Rename and Organize Columns (Optional)

For clarity:

• Rename the new column to WeekdayNumber or WeekdayName
• Move it next to your date column for easier inspection

This makes your dataset more intuitive and improves usability across reports.

Step 5: Close and Apply Changes

Once filtering is complete:

1. Click Close & Apply in the Power Query Editor
2. Your filtered dataset is now ready for use in reports and visuals

All reports and DAX measures will now reflect the filtered weekday data.

Refresh Tip: This filtering step is applied during data load, so it’s automatically applied on every scheduled refresh.

Step 6: Use DAX to Filter Weekdays (Alternative Method)

If you prefer to filter dynamically in DAX (instead of Power Query), you can create a calculated column:

Example DAX:

IsWeekday = IF(WEEKDAY(‘Sales'[OrderDate], 2) < 6, “Yes”, “No”)

This sets Monday = 1 and Sunday = 7, using the WEEKDAY(…, 2) pattern.

Then, filter visuals or measures based on IsWeekday = “Yes”.

Advanced Tip: Use DAX when you need user-controlled filters (e.g., slicers for weekday vs weekend) at the report level rather than permanently in the query.

Final Thoughts

Filtering weekdays using queries in Microsoft Power BI is a powerful way to clean, refine, and focus your data for more accurate analysis. Whether you exclude weekends for business metrics or isolate specific days for trend analysis, this technique enhances both data quality and user experience.