Semana Fiscal

StephenGW ,

Pruebe el siguiente código:

let
// Enter the date in Advanced Editor for the first day of the earliest Fiscal Year in #date(yyyy,m,d) format
StartDate = #date(2021, 1, 3),
// Enter the desired range of years as List in Advanced Editor
YearRange = {2021..2050},
// In Advanced Editor, use one of the two options for this step (code out the one not used with //) to either hard code or dynamically calculate the years with 53 weeks. Substitute a different evaluation criterion as needed.
YearsWith53Weeks = {2020},
//YearsWith53Weeks = List.Select(YearRange, each Date.DayOfWeekName(#date(_,11,30)) = "Saturday"),
StartingTable = Table.FromColumns({YearRange}, {"Year"}),
AddNumberOfWeeksColumn = Table.AddColumn(StartingTable, "Weeks", each if List.Contains(YearsWith53Weeks, [Year]) then 53 else 52),
#"Changed Type4" = Table.TransformColumnTypes(AddNumberOfWeeksColumn,{{"Year", Int64.Type}, {"Weeks", Int64.Type}}),
// In the Advanced Editor, enter the two patterns for 52 and 53 week years as a list of weeks per fiscal month
AddListOfMonthAndWeekCounts = Table.AddColumn(#"Changed Type4", "Custom", each if [Weeks] = 53 then List.Zip({{1..12}, {4,4,5,4,4,5,4,4,5,4,5,5}}) else List.Zip({{1..12}, {4,5,4,4,5,4,4,5,4,4,5,4}})),
#"Expanded Custom" = Table.ExpandListColumn(AddListOfMonthAndWeekCounts, "Custom"),
#"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"FM", "NumWeeks"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"FM", Int64.Type}, {"NumWeeks", Int64.Type}}),
AddListOfWeeksColumn = Table.AddColumn(#"Changed Type", "WeekInFM", each {1..[NumWeeks]}),
#"Expanded Custom1" = Table.ExpandListColumn(AddListOfWeeksColumn, "WeekInFM"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom1",{{"WeekInFM", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Year"}, {{"All", each _, type table [Year=nullable number, Weeks=nullable number, FM=nullable number, NumWeeks=nullable number, WeekInFM=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All], "New FW Index", 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Year", "All"}),
    #"Expanded Custom2" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Year", "Weeks", "FM", "NumWeeks", "WeekInFM", "New FW Index"}, {"Year", "Weeks", "FM", "NumWeeks", "WeekInFM", "New FW Index"}),
AddWeekIndex = Table.AddIndexColumn(#"Expanded Custom2", "FW_Index", 1, 1, Int64.Type),
Add7DayListPerWeek = Table.AddColumn(AddWeekIndex, "WeekDay", each {1..7}),
#"Expanded WeekDay" = Table.ExpandListColumn(Add7DayListPerWeek, "WeekDay"),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded WeekDay",{{"WeekDay", Int64.Type}}),
RemoveUnneededColumns = Table.RemoveColumns(#"Changed Type2",{"Weeks", "NumWeeks", "WeekDay"}),
AddDayIndex = Table.AddIndexColumn(RemoveUnneededColumns, "DayIndex", 0, 1, Int64.Type),
AddDatesBasedOnStartDateAndDayIndex = Table.AddColumn(AddDayIndex, "Date", each Date.AddDays(StartDate, [DayIndex]), type date)
in
AddDatesBasedOnStartDateAndDayIndex

Si el problema aún no se resuelve, proporcione información detallada sobre el error o el resultado esperado que espera. Hágamelo saber de inmediato, esperando su respuesta.
Saludos
Winniz
Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.