cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Removing Weekend and Consecutive Holidays in custom column (power Query)

I have a "Employee" column with a start date to which i need to add 3 working days to each date. Also I have a Holiday table with Holiday dates.

What i have achieved so far, with an example is:

If the given date is August 13, 2024 (Tuesday). I add 3 days to it which result as August 16, 2024.

Then, merged the employee and holiday table and implemented this condition: If August 16, 2024 (Friday) is a Holiday. Then add one day to it. Which is a August 17, 2024 Saturday. Then i add 2 more days to it to make it the August 19, 2024 (Monday).

I need help with this situation:

Let's say August 19, 2024 (Monday) is also a holiday, then add one more day to it and make it a Tuesday (the next working day). So basically, i need to find the 4th working day from the given date. Thanks.

2 ACCEPTED SOLUTIONS
Super User

Hi @meghansh, check this

Lets imagine you have holidays these 4 days in January:

For 4th of January next 3rd working date will be 11th because:

5th = holiday

6th = weekend

7th = weekend

8th = holiday

9th - 11th are working days and you want 3rd working day so 11th

``````let
EmployeeTable = Table.FromList(List.Dates(#date(2024,1,1), 31, #duration(1,0,0,0)), (x)=> {x}, type table[Start Date=date]),
HolidayTable = Table.FromList({#date(2024,1,1), #date(2024,1,5), #date(2024,1,8), #date(2024,1,25)}, (x)=> {x}, type table[Date=date]),
StepBack = EmployeeTable,
InsertedDayName = Table.AddColumn(EmployeeTable, "Day Name", each Date.DayOfWeekName([Start Date], "en-US"), type text),
[ a = List.Dates([Start Date], 10, #duration(1,0,0,0)),
b = List.Select(List.Skip(a), (x)=> not List.Contains({5, 6}, Date.DayOfWeek(x, Day.Monday)) and not List.Contains(List.Buffer(HolidayTable[Date]), x)),
c = b{2}?
][c], type date)
in

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Resolver III

Hey,

A possible solution is the function i made.
1. It creates a small table of the 8 days after the input

2. it will first filter out weekend days and secondly dates that appear in the HolidayDates list.

3. It then returns the third remaining date in the table.

``````(vDate as date) =>

let
Dates = Table.FromList(List.Dates(vDate + #duration(1,0,0,0), 8, #duration(1,0,0,0)), Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
changetypes = Table.TransformColumnTypes(Dates, {"Date", type date}),
removeweekends = Table.SelectRows(add_weekday, each ([Weekday] <> 5 and [Weekday] <> 6) and (List.Contains(HolidayTable[Date], [Date]) = false)),
selectDate = Table.SelectRows(add_index, each ([Index] = 3))

in
selectDate[Date]{0}``````

2 REPLIES 2
Resolver III

Hey,

A possible solution is the function i made.
1. It creates a small table of the 8 days after the input

2. it will first filter out weekend days and secondly dates that appear in the HolidayDates list.

3. It then returns the third remaining date in the table.

``````(vDate as date) =>

let
Dates = Table.FromList(List.Dates(vDate + #duration(1,0,0,0), 8, #duration(1,0,0,0)), Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
changetypes = Table.TransformColumnTypes(Dates, {"Date", type date}),
removeweekends = Table.SelectRows(add_weekday, each ([Weekday] <> 5 and [Weekday] <> 6) and (List.Contains(HolidayTable[Date], [Date]) = false)),
selectDate = Table.SelectRows(add_index, each ([Index] = 3))

in
selectDate[Date]{0}``````

Super User

Hi @meghansh, check this

Lets imagine you have holidays these 4 days in January:

For 4th of January next 3rd working date will be 11th because:

5th = holiday

6th = weekend

7th = weekend

8th = holiday

9th - 11th are working days and you want 3rd working day so 11th

``````let
EmployeeTable = Table.FromList(List.Dates(#date(2024,1,1), 31, #duration(1,0,0,0)), (x)=> {x}, type table[Start Date=date]),
HolidayTable = Table.FromList({#date(2024,1,1), #date(2024,1,5), #date(2024,1,8), #date(2024,1,25)}, (x)=> {x}, type table[Date=date]),
StepBack = EmployeeTable,
InsertedDayName = Table.AddColumn(EmployeeTable, "Day Name", each Date.DayOfWeekName([Start Date], "en-US"), type text),
[ a = List.Dates([Start Date], 10, #duration(1,0,0,0)),
b = List.Select(List.Skip(a), (x)=> not List.Contains({5, 6}, Date.DayOfWeek(x, Day.Monday)) and not List.Contains(List.Buffer(HolidayTable[Date]), x)),
c = b{2}?
][c], type date)
in

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors