M Code for Dynamic Holiday Table Using Calendarific API

gistfile1.txt

// Brian Julius' code to create a holiday table for specified years and country using Calendarific.com API
// On line 6, enter the starting and ending years for which you want to retrieve holidays
// On line 7, enter the ISO2 country code for the courntry for which you want to retrieve holidays
// If you don't know your country's ISO2 code go to https://calendarific.com/ 
// Their API covers over 230 countries, 3300 states, and 100 languages
// On line 13, replace the word "dummy" with your Calenderific API key (no quotes, just paste it over "dummy")
// After the code runs, filter on primary_type to select the holidays you want to keep to feed into your Date table


let
    years = {2022..2023},
    country = "US",
    getHolidaysForYear = (year as number) =>
        [
        apiURL = "https://calendarific.com/api/v2/holidays?&api_key=dummy&country="& country & "&year=" & Text.From(year),
        rawJson = Web.Contents(apiURL),
        parsedJson = Json.Document(rawJson),
        holidays = parsedJson[response][holidays]
        ][holidays],
    allHolidays = List.Combine(List.Transform(years, each getHolidaysForYear(_))),
    holidayTable = Table.FromList(allHolidays, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expand = Table.ExpandRecordColumn(holidayTable, "Column1", {"name", "date", "primary_type", "urlid"}, {"name", "date", "primary_type", "urlid"}),
    ExpandDate = Table.TransformColumnTypes( Table.ExpandRecordColumn(Expand, "date", {"iso"}, {"date.iso"}), {"date.iso", Date.Type}),
    RemoveErrors = Table.RemoveRowsWithErrors(ExpandDate, {"date.iso"})
in
    RemoveErrors