Power Query Problems

How to Calculate Working Days and Holidays in Excel Power Query

Huda Qurayshi Oct 20, 2026 870 views

Question details

The user needs to calculate valid working days in Power Query by excluding non-working days (such as certain Mondays) and historical holidays, and then assign an index to fill down for continuous tracking.

How to Calculate Working Days and Holidays in Excel Power Query

Product: Excel Power Query
Device & OS: not provided
Scenario: Filtering out specific non-working days and holidays in a dataset to assign sequential indexes for valid workdays.
Observed behavior: The user wants to identify valid working days, skip excluded dates, and apply the previous available workday index to those excluded dates using Power Query data transformations.

Before you start

Ensure your dataset is successfully loaded into Power Query and includes columns identifying the day of the week, workday status, and holiday dates.

Solution 1Recommended

Filter Holidays and Assign Workday Indexes

Create a reference query to filter out excluded days, assign an index, and merge it back to the original dataset using a Left Outer Join.

This method utilizes a custom conditional column to flag non-working days and holidays. By filtering these out and adding an index, you can accurately track valid workdays and use the 'Fill Down' feature to map the correct working day sequence across skipped dates.

Create a Reference Query

In the Power Query Editor, right-click your original query (e.g., Query1) in the Queries pane, select 'Reference', and rename the newly created query to 'MyDays'.

Add a Custom Exclusion Column

Navigate to 'Add Column' > 'Custom Column'. Name the column 'Exclude' and enter the formula: = if ([Day of Week] = 3 and [Workday.1] = null) or ([Holiday.1] <> null) then 1 else 0.

Filter Out Non-Working Days

Click the drop-down arrow on the new 'Exclude' column header and uncheck the value '1' to filter out all non-working days and historical holidays.

Add an Index Column

Go to 'Add Column' > 'Index Column' > 'From 1'. This assigns a sequential numerical index to all valid working days that remain.

Merge and Fill Down

Create a new query by performing a Left Outer Join between your original Query1 and MyDays based on the date column. Expand the Index column from the merged MyDays table, select the Index column, and click 'Transform' > 'Fill' > 'Down' to carry the index forward to the excluded dates.

Filter Holidays and Assign Workday Indexes

Formula Customization: Adjust the [Day of Week] = 3 logic in the custom column formula depending on how your specific dataset numbers the days of the week.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Analysis

While Power Query is a feature specific to Microsoft Excel, WPS Office provides a robust, highly compatible, and free alternative for handling large datasets. You can easily use built-in functions like NETWORKDAYS to calculate working days without complex data modeling.

1. Open your Spreadsheet in WPS Office: Launch WPS Spreadsheet and open your .xlsx data file containing your dates and holidays.
2. Use the NETWORKDAYS Function: In a new cell, type =NETWORKDAYS(start_date, end_date, [holidays]). Select your date cells for the start and end dates.
3. Define Holiday Ranges: Select the range of cells that contain your list of historical holidays to automatically exclude them from the working day count.

Highly compatible with Microsoft Excel (.xlsx) formats and formulas.Built-in advanced date and time functions for effortless workday calculations.Lightweight software with an intuitive, familiar interface.Free to download and use for your daily spreadsheet and data analysis tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Can I calculate working days without using Power Query?

Yes, you can use the built-in NETWORKDAYS or NETWORKDAYS.INTL functions directly in your spreadsheet. These formulas calculate the number of working days between two dates while automatically excluding weekends and any specific holidays you highlight.

Why do I need to use 'Fill Down' for the index column?

'Fill Down' ensures that when a date is flagged as a holiday or non-working day (and therefore lacks its own generated index), it inherits the index of the most recent valid working day. This is crucial for continuous reporting and matching.

How does the custom formula identify specific days like Mondays?

In the provided formula, [Day of Week] = 3 assumes that '3' represents Monday in the dataset's day-of-week indexing. Depending on how your dates are formatted (e.g., if Sunday is 1, Monday is 2), you should adjust this number to match your desired weekday.

What does a Left Outer Join do in this process?

A Left Outer Join preserves all the rows (dates) from your original complete calendar query and matches them with the filtered, indexed working days from the reference query. This allows you to bring the new Index column into your full calendar view.

Excel & Data Problems