Power Query创建含加拿大节假日的永久日历：疑难节假日集成求助

核心思路 #

创建可复用的M函数，接收起止日期与可选临时节假日列表，按四类规则生成指定时间段内的所有加拿大节假日，输出含「日期」「节假日名称」的表格，可直接与现有永久日历函数合并。

完整函数代码 #

(StartDate as date, EndDate as date, optional TemporaryHolidays as list) as table =>
let
    // 生成日期范围内的年份列表
    YearsList = List.Generate(
        () => Date.Year(StartDate),
        each _ <= Date.Year(EndDate),
        each _ + 1
    ),

    // 1. 固定日期类节假日
    FixedDateHolidays = List.Combine(List.Transform(YearsList, (y) => {
        {#date(y, 1, 1), "元旦"},
        {#date(y, 6, 24), "圣让巴蒂斯特日"},
        {#date(y, 7, 1), "加拿大日"},
        {#date(y, 9, 30), "真相与和解日"},
        {#date(y, 11, 11), "阵亡将士纪念日"},
        {#date(y, 12, 25), "圣诞节"},
        {#date(y, 12, 26), "节礼日"}
    })),

    // 2. 固定星期类节假日
    FixedWeekHolidays = List.Combine(List.Transform(YearsList, (y) => {
        // 9月第一个周一：劳动节
        {Date.AddDays(#date(y, 9, 1), (8 - Date.DayOfWeek(#date(y, 9, 1), Day.Monday)) mod 7), "劳动节"},
        // 10月第二个周一：感恩节
        {Date.AddDays(#date(y, 10, 1), (8 - Date.DayOfWeek(#date(y, 10, 1), Day.Monday)) mod 7 + 7), "感恩节"}
    })),

    // 辅助函数：高斯算法计算复活节日期
    GetEasterSunday = (year as number) as date =>
    let
        a = year mod 19,
        b = Number.IntegerDivide(year, 100),
        c = year mod 100,
        d = Number.IntegerDivide(b, 4),
        e = b mod 4,
        f = Number.IntegerDivide(b + 8, 25),
        g = Number.IntegerDivide(b - f + 1, 3),
        h = (19*a + b - d - g + 15) mod 30,
        i = Number.IntegerDivide(c, 4),
        k = c mod 4,
        l = (32 + 2*e + 2*i - h - k) mod 7,
        m = Number.IntegerDivide(a + 11*h + 22*l, 451),
        month = Number.IntegerDivide(h + l - 7*m + 114, 31),
        day = (h + l - 7*m + 114) mod 31 + 1
    in
        #date(year, month, day),

    // 3. 日期浮动类节假日
    FloatingHolidays = List.Combine(List.Transform(YearsList, (y) =>
    let
        Easter = GetEasterSunday(y),
        GoodFriday = Date.AddDays(Easter, -2),       // 耶稣受难日
        EasterMonday = Date.AddDays(Easter, 1),      // 复活节次日
        VictoriaDay = Date.AddDays(#date(y, 5, 25), -Date.DayOfWeek(#date(y, 5, 25), Day.Monday)) // 维多利亚日
    in
        {
            {GoodFriday, "耶稣受难日"},
            {Easter, "复活节"},
            {EasterMonday, "复活节次日"},
            {VictoriaDay, "维多利亚日"}
        }
    )),

    // 4. 临时纪念类节假日（处理可选参数）
    TempHolidays = if TemporaryHolidays = null then {} else TemporaryHolidays,

    // 合并并清洗数据
    AllHolidaysList = FixedDateHolidays & FixedWeekHolidays & FloatingHolidays & TempHolidays,
    HolidaysTable = Table.FromList(AllHolidaysList, Splitter.SplitByNothing(), {"HolidayRecord"}),
    ExpandColumns = Table.ExpandListColumn(HolidaysTable, "HolidayRecord"),
    SplitColumns = Table.SplitColumn(ExpandColumns, "HolidayRecord", Splitter.SplitByDelimiter(",", QuoteStyle.Csv), {"Date", "HolidayName"}),
    ConvertTypes = Table.TransformColumnTypes(SplitColumns, {{"Date", type date}, {"HolidayName", type text}}),
    FilterDateRange = Table.SelectRows(ConvertTypes, each [Date] >= StartDate and [Date] <= EndDate),
    RemoveDuplicates = Table.Distinct(FilterDateRange, {"Date"})
in
    RemoveDuplicates

使用说明 #

1. 参数说明

   • StartDate：必填，节假日范围起始日期（示例：#date(2020,1,1)）
   • EndDate：必填，节假日范围结束日期（示例：#date(2030,12,31)）
   • TemporaryHolidays：可选，临时节假日列表（示例：{{#date(2022,9,19), "伊丽莎白二世国丧日"}}）

2. 与永久日历合并
   在现有日历函数中，调用此节假日函数获取表格后，使用Table.Join按「日期」列合并两张表，即可为日历添加节假日标识。

3. 调休规则补充
   若需处理节假日落在周末的调休（如加拿大日遇周日次日补休），可在固定日期类逻辑中添加判断，示例：

   // 加拿大日调休处理示例
   let
       CanadaDay = #date(y,7,1),
       AdjustedDay = if Date.DayOfWeek(CanadaDay, Day.Sunday)=0 then Date.AddDays(CanadaDay,1) else if Date.DayOfWeek(CanadaDay, Day.Saturday)=6 then Date.AddDays(CanadaDay,-1) else CanadaDay
   in
       {AdjustedDay, "加拿大日"}
   

内容的提问来源于stack exchange，提问作者Rudy