Week Number is INCORRECT

Hey Guys,

Good day. I would like to ask for some help, because I would like to create a column for WEEK Number for each dates, but I am getting incorrect values, e.g : March 26, 2017 to March 31, 2017, I am expecting to get WEEK 5 for those dates, but I am getting WEEK 1 which INCORRECT.

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])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
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", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertYearMonth = Table.AddColumn(InsertCalendarMonth, "YearMonthCalendar", each [Year] * 100 + [MonthOfYear]),
InsertCalendarQtr = Table.AddColumn(InsertYearMonth, "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", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date)

Source = Query1(#date(2017, 1, 1), #date(2017, 12, 31), null),
#"Inserted Week of Month" = Table.AddColumn(Source, "WeekOfMonth", each Date.WeekOfMonth([WeekEnding]), type number),
#"Added Custom" = Table.AddColumn(#"Inserted Week of Month", "WeekNameMonth", each if [WeekOfMonth] = 1 then "Week 1"
else if [WeekOfMonth] = 2 then "Week 2"
else if [WeekOfMonth] = 3 then "Week 3"
else "Week 4"),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"WeekNameMonth", "Weeks"}, {"DayOfWeekName", "Day of the Week"}})

SEE attached screenshot

THANK YOU!!!

Anonymous,

In your custom column WeekOfMonth, why do you use WeekEnding argument in Date.WeekOfMonth function, you could use Date on that fucntion. I have tested it on my local environment, please refer to the screenshot below.

Regards,

Charlie Liao