Customising Date table column - Identify specific weekday in every month

Power Query
1

Hi all

As part of transformations I am doing in Power Query, I would like to show a list of rows in a date table that identify a recurring and specific day in each month e.g. the 3rd Thursday in every month, for initially this year, and subsequent years.

E.g. for 2021 this would leave the following rows:
21/01/2021
18/02/2021
18/03/2021
15/04/2021
20/05/2021 etc.

I am using Melissa’s awesome date table, but I have no idea how to do that.
Can someone suggest a dynamic way to achieve the result?
Sample dataset attached.

Thanks for your time.

Meetings Example.pbix (387.8 KB) Example Meeting.xlsx (11.5 KB)

2

Hi @Mo.jo.jo,

You can try something like this. This returns a boolean and a date column.

let
    GroupRows = Table.Group(#"Removed Other Columns", {"MonthnYear", "DayOfWeekName"}, {{"AllRows", each _, type table [Date=nullable date, Year=nullable number, YearOffset=nullable number, QuarterOfYear=nullable number, #"Quarter & Year"=text, QuarternYear=nullable number, MonthOfYear=nullable number, DayOfMonth=nullable number, Month Name=text, MonthShortName=text, #"Month & Year"=text, MonthnYear=nullable number, MonthCompleted=logical, MonthEnding=date, DateInt=nullable number, DayOfWeek=nullable number, DayOfWeekName=text, Day Type=text, ISO Year=number, ISO YearOffset=number, ISO QuarterOfYear=number, ISO Quarter=text, #"ISO Quarter & Year"=text, ISO QuarternYear=number, ISO QuarterOffset=number, ISO Weeknumber=nullable number, #"Week & Year"=text, WeeknYear=nullable number, WeekCompleted=logical, WeekEnding=date, IsCurrentFW=logical, IsAfterToday=logical, IsWorkingDay=logical, IsHoliday=text]}}),
    FilterDay = Table.SelectRows(GroupRows, each ([DayOfWeekName] = "Thursday")),
    GetTarget = Table.AddColumn(FilterDay, "TargetDate", each [AllRows]{2}[Date])[TargetDate],
    BufferDates = List.Buffer(GetTarget),

    Source = FxDateTable(#date(2021, 1, 1), #date(2021, 12, 31), null, null, null),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Date", "Year", "YearOffset", "QuarterOfYear", "Quarter & Year", "QuarternYear", "MonthOfYear", "DayOfMonth", "Month Name", "MonthShortName", "Month & Year", "MonthnYear", "MonthCompleted", "MonthEnding", "DateInt", "DayOfWeek", "DayOfWeekName", "Day Type", "ISO Year", "ISO YearOffset", "ISO QuarterOfYear", "ISO Quarter", "ISO Quarter & Year", "ISO QuarternYear", "ISO QuarterOffset", "ISO Weeknumber", "Week & Year", "WeeknYear", "WeekCompleted", "WeekEnding", "IsCurrentFW", "IsAfterToday", "IsWorkingDay", "IsHoliday"}),
    Add3rdThursday = Table.AddColumn(#"Removed Other Columns", "3rd Thursday boolean", each List.Contains( BufferDates, [Date]), type logical),
    Add3rdThursday2 = Table.AddColumn(Add3rdThursday, "3rd Thursday Date", each if List.Contains( BufferDates, [Date]) then [Date] else null, type date )
in
    Add3rdThursday2

I hope this is helpful.

2 Likes
3

@Mo.jo.jo,

I used the Practice Dataset External tool to create an example using Melissa’s extended date table (sorry, completely missed your PBIX, but thanks for that!)

Started with Groupby:

image
image1049×692 15 KB

Added a custom index column to count the weekday name occurences in each grouped month:

image
image1049×656 20.5 KB

And the just did a column by examples to convert 1 to First, 2 to Second

image

I hope this is helpful. Full solution file attached.

  • Brian

eDNA Forum - Day of Week Index Solution.pbix (380.3 KB)

2 Likes
4

Ha! I knew my chances of chiming in on this one before @Melissa were slim and none. :laughing:

Interesting to see the very different approaches taken.

  • Brian
1 Like
5

Jeez.
Just that … jeez.
Ever saving bacon’s and hair-pulling elegantly.

Thank you very much. :clap: :clap:

6

Thank you Brian.
Ingenious approach as usual.
You show that there are many ways to bake a known unknown pie :smile:

7

:rofl:

No complaints from all parties then.
Thank you both.

8

Hi @Mo.jo.jo,

Alternatively you can use this custom function.
Date needs to be a date value, and StartOfWeekNum a number where mon =1…sun =7

( Date as date, StartOfWeekNum as number, Occurence as number )  as nullable date  =>
  let
      nStartOfWeekNum = if List.Contains({1..7}, StartOfWeekNum ) = true then StartOfWeekNum -1 else 0,
      nOccurence = if List.Contains({1..5}, Occurence ) = true then Occurence -1 else 0,
      GetDate = List.Select( List.Dates( Date.StartOfMonth( Date ), Number.From( Date.EndOfMonth( Date ) - Date.StartOfMonth( Date )) +1, Duration.From(1) ), each Date.DayOfWeek(_, Day.Monday) = nStartOfWeekNum ){nOccurence}?
  in
      GetDate

I hope this is helpful.

1 Like