Date Table - EndOfWeek Day Change after specified date

Power Query
1

Hello,

I have been using Sam’s date table for several years and at the end of 2022 my week format is changing from Sun - Sat to Mon - Sun. Currently the code uses this line for WeekEnding column here:

InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),

Starting after WeekEnding 12/24/22 I would like to use:

InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date], Day.Monday), type date),

In order to maintain integrity for past dates I am trying to switch week format in the middle of the date table using which doesn’t work:

InsertWeekEnding = if (each Date.EndOfWeek([Date]) <= Date.FromText("12-24-2022")) then
         Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date) else
         Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date], Day.Monday), type date),

Full table here:

= (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
  let
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),   
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),
    InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
    InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
    //InsertWeekEnding = if each (each Date.EndOfWeek([Date]) <= Date.FromText("12-24-2022")) then
    //     Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date) else
    //     Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date], Day.Monday), type date),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date], Day.Monday), type date),
    InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date])),
    InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
    InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),
    ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),
    InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),
    AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+0) else [ShortYear]))
in
    AddFY

Thanks for your help!
Jonathan

1 Like
2

Hi Jonathan,

this is your code translated to a correctly defined if statement:

= Table.AddColumn(InsertDayName, "WeekEnding", each if Date.EndOfWeek([Date], 0) <= Date.FromText("12-24-2022") then
         Date.EndOfWeek([Date], 0) else
         Date.EndOfWeek([Date], 1), type date)

[I defined the beginning of the week for all 3 Date.EndOfWeek(), to get a culture independent code!]

You would get one row for the transition of the end of week date.
image

This single transitional end of week row can be brought to any position by adjusting the formula. I guess that is what you wanted to achieve.

In case this it not what you wanted to achieve, then it would still be the correct transformation of your attempt to build the if condition. => Adapt to your needs. :blush:

Regards,
Matthias

3

Matthias, thanks for the help, it is working just as I needed. I appreciate the quick reply.

Jonathan

1 Like