Power query week of month sequence

[imranamikhan]

Hi all,

I have created a week of month column by simply using the Date.WeekOfMonth function to return the week number per date.

However, where a week crosses months, I want the week number to continue its sequence and then reset the following Monday (my start day of the week).

Example below with the result I am trying to achieve with Power Query:

[lbendlin · Accepted solution]

This data is immutable. Don't waste your time trying to do this in Power Query (It may not even be possible since you would have to look back into prior months' week numbering, recursively).  Use an external Calendar reference table that has this precomputed.

For reference here's a function that tries and fails miserably.

let
    Source = List.Dates(#date(2024,1,1),71,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date.WeekOfMonth", each Date.WeekOfMonth([Column1],Day.Monday)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "WeekDayFirstOfMonth", each Date.DayOfWeek(Date.StartOfMonth([Column1]),Day.Monday)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "AdjustedWeekOfMonth", each if [WeekDayFirstOfMonth]=0 then [Date.WeekOfMonth] else if Date.Day([Column1])<=(7-[WeekDayFirstOfMonth]) then 5 else [Date.WeekOfMonth]-1)
in
    #"Added Custom2"

[imranamikhan · Accepted solution]

@lbendlin - I went back to the drawing board and started thinking about how I would do this in Excel. I ended up with the following Excel formula:

=QUOTIENT(DAY([@Date]-WEEKDAY([@Date],3))-1,7)+1

The above achieved the result I needed in the spreadsheet. I then translated the above with equivalent functions in Power Query using:

let 

getWkDay = Date.DayOfWeek([Date], Day.Monday), 

getMonday = Date.AddDays(Date.From([Date]), - getWkDay),

prvMondayMinusOne = Date.Day(getMonday) - 1,

result = Number.IntegerDivide(PrvMondayMinusOne, 7) + 1 //count number of complete weeks up to this date

in result

Hopefully this will help anyone else trying to achieve a similar result.

[imranamikhan]

Thanks for the advice @lbendlin.

I have a date reference table I am using and I am attempting to add this type of sequencing for week numbers as a new column into the date reference table (but without success).

Annoyingly I cannot locate a calendar reference table online with this type of sequencing for week numbers.

Knowing you think this is a futile exercise, I will instead populate this column manually with the sequencing I need.

[lbendlin]

Yes, I truly believe this is a futile exercise.  Were I to do it I would for a given date find the latest prior month that starts on a monday,  and then would use List.Accumulate to conditionally count the weeks and days until the current day.  What a glorious waste of time 🙂

let
  Source = List.Dates(#date(2024, 1, 1), 71, #duration(1, 0, 0, 0)), 
  #"Converted to Table" = Table.FromList(
    Source, 
    Splitter.SplitByNothing(), 
    {"Date"}, 
    null, 
    ExtraValues.Error
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Converted to Table", 
    "WeekOfMonth", 
    each List.Accumulate(
      {Int64.From(#date(2024, 1, 1)) .. Int64.From([Date])}, 
      0, 
      (state, current) =>
        if Date.DayOfWeek(Date.From(current), Day.Monday) > 0 then
          state
        else if Date.Day(Date.From(current)) < 7 then
          1
        else
          state + 1
    )
  )
in
  #"Added Custom"

[lbendlin · Accepted solution]

This data is immutable. Don't waste your time trying to do this in Power Query (It may not even be possible since you would have to look back into prior months' week numbering, recursively).  Use an external Calendar reference table that has this precomputed.

For reference here's a function that tries and fails miserably.

let
    Source = List.Dates(#date(2024,1,1),71,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date.WeekOfMonth", each Date.WeekOfMonth([Column1],Day.Monday)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "WeekDayFirstOfMonth", each Date.DayOfWeek(Date.StartOfMonth([Column1]),Day.Monday)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "AdjustedWeekOfMonth", each if [WeekDayFirstOfMonth]=0 then [Date.WeekOfMonth] else if Date.Day([Column1])<=(7-[WeekDayFirstOfMonth]) then 5 else [Date.WeekOfMonth]-1)
in
    #"Added Custom2"