For a Capacity Planning tool job I had, I needed to take into consideration working days (Monday-Friday), the Year + ISO week and Holidays in some countries. I made a calendar on PowerBI (easy to be exported to .csv) with holidays for 3 countries during 5 years (variables set as country). The original project had 6 countries, but it takes very long to load because its 6x5=30 web queries. I also wouldn't recommend anything larger than that per PowerBI
ISO week formula: Week = YEAR('Calendar'[Date]-WEEKNUM('Calendar'[Date],21)+26) & "/" & format(WEEKNUM('Calendar'[Date],21),"00")
ISO Year formula: ISOMonth = left('Calendar'[Week],5) & format(MONTH(DATE(year('Calendar'[Date]),1,WEEKNUM('Calendar'[Date],21)*7-2)-WEEKDAY(DATE(year('Calendar'[Date]),1,3))),"00")
The file is here with the years 2022 - 2026 and Holidays for The Netherlands, Germany and USA. Feel free to modify the variable list in the Power Query editor.
If you just want the code to retrieve the holidays from the internet the script is:
let
kept = "Public Holiday",
Source = Web.BrowserContents("https://www.timeanddate.com/holidays/" & (#"Country 1" as text) &"/" & (#"Year 1" as text) &"?hol=9"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE[id='holidays-table'] > * > TR > TH[rowspan=""2""]:not([colspan]):nth-child(1):nth-last-child(4), TABLE[id='holidays-table'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(4)"}, {"Column2", "TABLE[id='holidays-table'] > * > TR > TH[rowspan=""2""]:not([colspan]):nth-child(1):nth-last-child(4) + TH[rowspan=""2""]:not([colspan]):nth-child(2):nth-last-child(3), TABLE[id='holidays-table'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(3)"}, {"Column3", "TABLE[id='holidays-table'] > * > TR > TH[rowspan=""2""]:not([colspan]):nth-child(1):nth-last-child(4) + TH[rowspan=""2""]:not([colspan]):nth-child(2):nth-last-child(3) + TH[rowspan=""2""]:not([colspan]):nth-child(3):nth-last-child(2), TABLE[id='holidays-table'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(2)"}, {"Column4", "TABLE[id='holidays-table'] > * > TR > TH[rowspan=""2""]:not([colspan]):nth-child(1):nth-last-child(4) + TH[rowspan=""2""]:not([colspan]):nth-child(2):nth-last-child(3) + TH[rowspan=""2""]:not([colspan]):nth-child(3):nth-last-child(2) + TH[rowspan=""2""]:not([colspan]):nth-child(4):nth-last-child(1), TABLE[id='holidays-table'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(2) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(1)"}}, [RowSelector="TABLE[id='holidays-table'] > * > TR"]),
#"Removed Blank Rows" = Table.SelectRows(#"Extracted Table From Html", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Blank Rows", [PromoteAllScalars=true]),
#"Added Suffix" = Table.TransformColumns(#"Promoted Headers", {{"Date", each _ & (#"Year 1" as text), type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Added Suffix",{{"Date", type date}}),
#"Inserted Day of Week" = Table.AddColumn(#"Changed Type", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Added Country" = Table.AddColumn(#"Inserted Day of Week", "Country", each (#"Country 1" as text), type text),
#"Filtered Weekend" = Table.SelectRows(#"Added Country", each [Day of Week] <> 5 and [Day of Week] <> 6),
#"Filtered Public" = Table.SelectRows(#"Filtered Weekend", each Text.Contains([Type], kept)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Public",{"Date", "Name","Country"})
in
#"Removed Other Columns"
Comments