Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
meghansh
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
dufoq3
Super User
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

 

dufoq3_0-1723274012207.png

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),
    Ad_NextWorkingDate = Table.AddColumn(InsertedDayName, "Next Working Date", each 
        [ 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
    Ad_NextWorkingDate

 


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

View solution in original post

Chewdata
Resolver III
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.

func_AddDate = 

 

(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}),
add_weekday = Table.AddColumn(changetypes, "Weekday", each Date.DayOfWeek([Date], Day.Monday)),
removeweekends = Table.SelectRows(add_weekday, each ([Weekday] <> 5 and [Weekday] <> 6) and (List.Contains(HolidayTable[Date], [Date]) = false)),
add_index = Table.AddIndexColumn(removeweekends, "Index", 1, 1),
selectDate = Table.SelectRows(add_index, each ([Index] = 3))


in
selectDate[Date]{0}

 
in the main query add: 
Table.AddColumn(PREVSTEP, "New Date", each func_AddDate(old_date))


View solution in original post

2 REPLIES 2
Chewdata
Resolver III
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.

func_AddDate = 

 

(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}),
add_weekday = Table.AddColumn(changetypes, "Weekday", each Date.DayOfWeek([Date], Day.Monday)),
removeweekends = Table.SelectRows(add_weekday, each ([Weekday] <> 5 and [Weekday] <> 6) and (List.Contains(HolidayTable[Date], [Date]) = false)),
add_index = Table.AddIndexColumn(removeweekends, "Index", 1, 1),
selectDate = Table.SelectRows(add_index, each ([Index] = 3))


in
selectDate[Date]{0}

 
in the main query add: 
Table.AddColumn(PREVSTEP, "New Date", each func_AddDate(old_date))


dufoq3
Super User
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

 

dufoq3_0-1723274012207.png

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),
    Ad_NextWorkingDate = Table.AddColumn(InsertedDayName, "Next Working Date", each 
        [ 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
    Ad_NextWorkingDate

 


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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

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

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors