445 Calendar ISO YEAR - Calendário Comercial 445

[bonafides]

Hey Guys, I've got this error in my 445 calendar, column "ANO ISO" .Years (2018, 2019, 2020) working perfect but 2021 with problem. I did something just to go on while not fixed at all. 

let
    Source = List.Buffer(CALENDARIO[DATA]),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DATA"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"DATA", type date}}),
    InsertDayID = Table.AddIndexColumn(#"Changed Type1", "DayID", 1, 1, Int64.Type),
    InsertYearID = Table.AddColumn(InsertDayID, "YearID", each Number.RoundDown(([DayID]-1)/364)+1),
    InsertMonthID = Table.AddColumn(InsertYearID, "MonthID", each Number.RoundDown([DayID]/91)*3+
( if Number.Mod([DayID],91)=0 then 0
else if Number.Mod([DayID],91)<=28 then 1
else if Number.Mod([DayID],91)<=56 then 2
else 3
)),
    InsertMonthNumber = Table.AddColumn(InsertMonthID, "MonthNumber", each [MonthID] - (Number.RoundUp([MonthID]/12)-1)*12),
    InsertQuarterID = Table.AddColumn(InsertMonthNumber, "QuarterID", each Number.RoundDown(([DayID]-1)/91)+1),
    InsertQuarterNumber = Table.AddColumn(InsertQuarterID, "Quarter Number", each [QuarterID] - Number.RoundUp(([QuarterID]/4)-1)*4),
    InsertWeekID = Table.AddColumn(InsertQuarterNumber, "WeekID", each Number.RoundDown(([DayID]-1)/7)+1),
    InsertWeekNumber = Table.AddColumn(InsertWeekID, "WeekNumber", each [WeekID] - Number.RoundUp(([WeekID]/52)-1)*52),
    InsertWeekofQuarter = Table.AddColumn(InsertWeekNumber, "Week of Quarter", each [WeekID] - (Number.RoundUp([WeekID]/13)-1)*13),
    InsertedDaysInYear = Table.AddColumn(InsertWeekofQuarter, "DaysInYear", each Duration.Days( Date.EndOfYear([DATA]) - Date.StartOfYear([DATA]) )),
    #"InsertDayOfWeek" = Table.AddColumn(InsertedDaysInYear, "DayOfWeek", each Date.DayOfWeek([DATA], 1)),
    InsertAnoISO = Table.AddColumn(InsertDayOfWeek, "AnoISO", each if 
[DATA] = #date(2021, 1, 1) or [DATA] = #date(2021, 1, 2) or [DATA] = #date(2021, 1, 3) then 2021 else
if [DaysInYear] = 364 then
Date.Year(
            Date.AddDays(
                [DATA],
                3 - [DayOfWeek]
            )
        )
else
Date.Year(
            Date.AddDays(
                [DATA],
                10 - [DayOfWeek]
            )
        )),
    InsertISOYearDayNumber = Table.AddColumn(InsertAnoISO, "ISO Year Day Number", each ([WeekNumber]-1)*7+[DayOfWeek]+1),
    #"InsertSemanaInt" = Table.AddColumn(
        InsertISOYearDayNumber,
        "SemanaInt",
        each [AnoISO] * 100 + [WeekNumber],
        type number
    ),

    #"InsertCalendarSemana" = Table.AddColumn(
        InsertSemanaInt,
        "SemanaNoCalendario",
        each Number.ToText([AnoISO]) & Number.ToText([WeekNumber], "-W00")
    ),
    InsertMonthName = Table.AddColumn(InsertCalendarSemana, "MonthName", each if [MonthNumber] = 1 then "Janeiro" else if [MonthNumber] = 2 then "Fevereiro" else if [MonthNumber] = 3 then "Março" else if [MonthNumber] = 4 then "Abril" else if [MonthNumber] = 5 then "Maio" else if [MonthNumber] = 6 then "Junho" else if [MonthNumber] = 7 then "Julho" else if [MonthNumber] = 8 then "Agosto" else if [MonthNumber] = 9 then "Setembro" else if [MonthNumber] = 10 then "Outubro" else if [MonthNumber] = 11 then "Novembro" else if [MonthNumber] = 12 then "Dezembro" else "Inválido"),
    #"Changed Type" = Table.TransformColumnTypes(InsertMonthName,{{"MonthName", type text}, {"SemanaNoCalendario", type text}, {"MonthNumber", Int64.Type}, {"AnoISO", Int64.Type}}),
    RenomearColunas = Table.RenameColumns(#"Changed Type",{{"AnoISO", "ANO ISO"}, {"WeekNumber", "SEMANA ISO"}, {"SemanaInt", "SEMANA INT"}, {"ISO Year Day Number", "ISO YEAR DAY NUMBER"}, {"SemanaNoCalendario", "SEMANA NO CALENDARIO"}, {"MonthNumber", "NUM MÊS"}, {"MonthName", "NOME MÊS"}, {"Week of Quarter", "SEMANA TRIMESTRE"}, {"Quarter Number", "NUM TRIMESTRE"}, {"DaysInYear", "DIAS NO ANO"}, {"DayOfWeek", "DIA DA SEMANA"}}),
    #"Coluna Mesclada Inserida" = Table.AddColumn(RenomearColunas, "MÊS/ANO", each Text.Combine({Text.From([NUM MÊS], "pt-BR"), Text.From([ANO ISO], "pt-BR")}, "/"), type text),
    #"Personalização Adicionada" = Table.AddColumn(#"Coluna Mesclada Inserida", "CLASSIFICA MÊS/ANO", each 100 * [ANO ISO] + [NUM MÊS]),
    InsertSemanasnoMes = Table.AddColumn(#"Personalização Adicionada", "SEMANASNOMÊS", each if [NUM MÊS] = 1 then 4 else if [NUM MÊS] = 2 then 4 else if [NUM MÊS] = 3 then 5 else if [NUM MÊS] = 4 then 4 else if [NUM MÊS] = 5 then 4 else if [NUM MÊS] = 6 then 5 else if [NUM MÊS] = 7 then 4 else if [NUM MÊS] = 8 then 4 else if [NUM MÊS] = 9 then 5 else if [NUM MÊS] = 10 then 4 else if [NUM MÊS] = 11 then 4 else if [NUM MÊS] = 12 then 5 else 0),
    #"Personalização Adicionada1" = Table.AddColumn(InsertSemanasnoMes, "SEMANA DO MÊS", each if  [SEMANA ISO]  = 1 then 1  else
 if  [SEMANA ISO]  = 2 then 2  else
 if  [SEMANA ISO]  = 3 then 3  else
 if  [SEMANA ISO]  = 4 then 4  else
 if  [SEMANA ISO]  = 5 then 1  else
 if  [SEMANA ISO]  = 6 then 2  else
 if  [SEMANA ISO]  = 7 then 3  else
 if  [SEMANA ISO]  = 8 then 4  else
 if  [SEMANA ISO]  = 9 then 1  else
 if  [SEMANA ISO]  = 10 then 2 else
 if  [SEMANA ISO]  = 11 then 3 else
 if  [SEMANA ISO]  = 12 then 4 else
 if  [SEMANA ISO]  = 13 then 5 else
 if  [SEMANA ISO]  = 14 then 1 else
 if  [SEMANA ISO]  = 15 then 2 else
 if  [SEMANA ISO]  = 16 then 3 else
 if  [SEMANA ISO]  = 17 then 4 else
 if  [SEMANA ISO]  = 18 then 1 else
 if  [SEMANA ISO]  = 19 then 2 else
 if  [SEMANA ISO]  = 20 then 3 else
 if  [SEMANA ISO]  = 21 then 4 else
 if  [SEMANA ISO]  = 22 then 1 else
 if  [SEMANA ISO]  = 23 then 2 else
 if  [SEMANA ISO]  = 24 then 3 else
 if  [SEMANA ISO]  = 25 then 4 else
 if  [SEMANA ISO]  = 26 then 5 else
 if  [SEMANA ISO]  = 27 then 1 else
 if  [SEMANA ISO]  = 28 then 2 else
 if  [SEMANA ISO]  = 29 then 3 else
 if  [SEMANA ISO]  = 30 then 4 else
 if  [SEMANA ISO]  = 31 then 1 else
 if  [SEMANA ISO]  = 32 then 2 else
 if  [SEMANA ISO]  = 33 then 3 else
 if  [SEMANA ISO]  = 34 then 4 else
 if  [SEMANA ISO]  = 35 then 1 else
 if  [SEMANA ISO]  = 36 then 2 else
 if  [SEMANA ISO]  = 37 then 3 else
 if  [SEMANA ISO]  = 38 then 4 else
 if  [SEMANA ISO]  = 39 then 5 else
 if  [SEMANA ISO]  = 40 then 1 else
 if  [SEMANA ISO]  = 41 then 2 else
 if  [SEMANA ISO]  = 42 then 3 else
 if  [SEMANA ISO]  = 43 then 4 else
 if  [SEMANA ISO]  = 44 then 1 else
 if  [SEMANA ISO]  = 45 then 2 else
 if  [SEMANA ISO]  = 46 then 3 else
 if  [SEMANA ISO]  = 47 then 4 else
 if  [SEMANA ISO]  = 48 then 1 else
 if  [SEMANA ISO]  = 49 then 2 else
 if  [SEMANA ISO]  = 50 then 3 else
 if  [SEMANA ISO]  = 51 then 4 else
 if  [SEMANA ISO]  = 52 then 5 else
 0),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Personalização Adicionada1",{{"MÊS/ANO", type text}, {"CLASSIFICA MÊS/ANO", Int64.Type}, {"SEMANASNOMÊS", Int64.Type}, {"DATA", type date}, {"DayID", Int64.Type}, {"YearID", Int64.Type}, {"MonthID", Int64.Type}, {"NUM MÊS", Int64.Type}, {"QuarterID", Int64.Type}, {"NUM TRIMESTRE", Int64.Type}, {"WeekID", Int64.Type}, {"SEMANA ISO", Int64.Type}, {"SEMANA TRIMESTRE", Int64.Type}, {"DIAS NO ANO", Int64.Type}, {"DIA DA SEMANA", Int64.Type}, {"ANO ISO", Int64.Type}, {"SEMANA INT", Int64.Type}, {"SEMANA NO CALENDARIO", type text}, {"NOME MÊS", type text}, {"SEMANA DO MÊS", Int64.Type}, {"ISO YEAR DAY NUMBER", Int64.Type}}),
    #"Colunas Removidas" = Table.RemoveColumns(#"Tipo Alterado",{"DayID", "YearID", "MonthID", "QuarterID", "WeekID"}),
    #"Coluna Mesclada Inserida1" = Table.AddColumn(#"Colunas Removidas", "Mês/Ano NUM", each Text.Combine({Text.From([NUM MÊS], "pt-BR"), Text.From([ANO ISO], "pt-BR")}, ""), type text),
    #"Coluna Duplicada" = Table.DuplicateColumn(#"Coluna Mesclada Inserida1", "SEMANA DO MÊS", "SEMANA DO MÊS - Copiar"),
    #"Colunas Renomeadas" = Table.RenameColumns(#"Coluna Duplicada",{{"SEMANA DO MÊS - Copiar", "SEMANA DO MÊS NOME"}}),
    #"Prefixo Adicionado" = Table.TransformColumns(#"Colunas Renomeadas", {{"SEMANA DO MÊS NOME", each "Semana " & Text.From(_, "pt-BR"), type text}}),
    #"Linhas Classificadas" = Table.Sort(#"Prefixo Adicionado",{{"DATA", Order.Ascending}})
in
    #"Linhas Classificadas"

Atenciosamente.,

Sóstenes Silva

[lbendlin]

Ask whoever created that script why this step is there:

 #"InsertDayOfWeek" = Table.AddColumn(InsertedDaysInYear, "DayOfWeek", each Date.DayOfWeek([DATA], 1)),
    InsertAnoISO = Table.AddColumn(InsertDayOfWeek, "AnoISO", each if 
[DATA] = #date(2021, 1, 1) or [DATA] = #date(2021, 1, 2) or [DATA] = #date(2021, 1, 3) then 2021 else
if [DaysInYear] = 364 then
Date.Year(
            Date.AddDays(
                [DATA],
                3 - [DayOfWeek]
            )
        )
else
Date.Year(
            Date.AddDays(
                [DATA],
                10 - [DayOfWeek]
            )
        )),

[bonafides · Accepted solution]

Because when year is leap year, this step doesnt work at all.