fnHolidaysInPeriodEN

fnHolidaysInPeriodEN(DataInicial as date, DataFinal as date) as table

let
    fnHolidaysInPeriodEN = (DataInicial as date, DataFinal as date) as table =>
        let
            // Enésima ocorrência de um dia da semana no mês (0 = segunda ... 6 = domingo)
            fnEnesimo = (ano as number, mes as number, dia as number, n as number) as date =>
                let
                    vPrimeiro = #date(ano, mes, 1),
                    vDesloc = Number.Mod(dia - Date.DayOfWeek(vPrimeiro, Day.Monday) + 7, 7)
                in
                    Date.AddDays(vPrimeiro, vDesloc + 7 * (n - 1)),
            // Última ocorrência de um dia da semana no mês
            fnUltimo = (ano as number, mes as number, dia as number) as date =>
                let
                    vUltimo = Date.EndOfMonth(#date(ano, mes, 1)),
                    vDesloc = Number.Mod(Date.DayOfWeek(vUltimo, Day.Monday) - dia + 7, 7)
                in
                    Date.AddDays(vUltimo, -vDesloc),
            // Data fixa no sábado → folga na sexta; no domingo → folga na segunda
            fnObservado = (d as date) as date =>
                let
                    vDia = Date.DayOfWeek(d, Day.Monday)
                in
                    if vDia = 5 then Date.AddDays(d, -1) else if vDia = 6 then Date.AddDays(d, 1) else d,
            // Linhas {DateReference, HolidayEN, ObservedDate} de um ano
            fnAno = (y as number) as list =>
                let
                    vFixos =
                        {{#date(y, 1, 1), "New Year's Day"}}
                        & (if y >= 2021 then {{#date(y, 6, 19), "Juneteenth National Independence Day"}} else {})
                        & {
                            {#date(y, 7, 4), "Independence Day"},
                            {#date(y, 11, 11), "Veterans Day"},
                            {#date(y, 12, 25), "Christmas Day"}
                        },
                    vMoveis = {
                        {fnEnesimo(y, 1, 0, 3), "Martin Luther King Jr. Day"},
                        {fnEnesimo(y, 2, 0, 3), "Presidents' Day"},
                        {fnUltimo(y, 5, 0), "Memorial Day"},
                        {fnEnesimo(y, 9, 0, 1), "Labor Day"},
                        {fnEnesimo(y, 10, 0, 2), "Columbus Day"},
                        {fnEnesimo(y, 11, 3, 4), "Thanksgiving Day"}
                    }
                in
                    List.Transform(vFixos, each {_{0}, _{1}, fnObservado(_{0})})
                    & List.Transform(vMoveis, each {_{0}, _{1}, _{0}}),
            vAnos = if DataFinal < DataInicial then {} else {Date.Year(DataInicial) .. Date.Year(DataFinal)},
            vTabela = #table(
                type table [DateReference = date, HolidayEN = text, ObservedDate = date],
                List.Combine(List.Transform(vAnos, fnAno))
            ),
            vPeriodo = Table.SelectRows(vTabela, each [DateReference] >= DataInicial and [DateReference] <= DataFinal)
        in
            Table.Sort(vPeriodo, {{"DateReference", Order.Ascending}}),

    Documentacao = [
        Documentation.Name = "fnHolidaysInPeriodEN",
        Documentation.Description = "Feriados federais dos EUA entre duas datas, com a data de folga observada.",
        Documentation.LongDescription = "Colunas DateReference, HolidayEN e ObservedDate. Feriado de data fixa no sábado é observado na sexta; no domingo, na segunda. Juneteenth a partir de 2021. Período sem feriado retorna tabela vazia com as colunas.",
        Documentation.Category = "Data e hora",
        Documentation.Author = "Paulo André Jesus",
        Documentation.Version = "2.0",
        Documentation.Examples = {
            [Description = "2026", Code = "fnHolidaysInPeriodEN(#date(2026, 1, 1), #date(2026, 12, 31))",
                Result = "11 linhas; Independence Day em 04/07/2026 (sábado) com ObservedDate 03/07/2026; Thanksgiving em 26/11/2026"]
        }
    ]
in
    Value.ReplaceType(fnHolidaysInPeriodEN, Value.ReplaceMetadata(Value.Type(fnHolidaysInPeriodEN), Documentacao))

fnHolidaysInPeriodEN(#date(2026, 1, 1), #date(2026, 12, 31))
// 11 linhas; Independence Day 04/07 (sábado) com ObservedDate 03/07; Thanksgiving 26/11