Add Column to Date Table

[mib1019]

I have a Power Query Date Table, Year of Dates, which I will update parameters to get next year's list.

The table contains all dates for the year and includes Month #, Month (text MMM), Weekday #, and Day (text ddd). The text field will be used to build a string after the next calculation(s).

I need to calculate the nth of each date in its month, e.g. 1/2/24 is the 2nd Tue of Jan, and 1/30/24 is the Last. Some Last weekdays will be the 4th, and some will be the 5th.

The last step, after 1st, 2nd, 3rd etc. will be the text string e.g. 2nd Tue in Jan, Last Tue in Jan.

I hope this makes sense. Thanks in advance for any advice/help you can give.

Thanks!
Mary B.

 

[Matt Allington]

Why do you need next year’s list? Take a look at my article here.

Build a Reusable Calendar Table with Power Query

In this step by step guide you'll learn how to create a Calendar Table using Power Query and save the code to reuse it later.

exceleratorbi.com.au

 

[mib1019]

Why do you need next year’s list? Take a look at my article here.

Build a Reusable Calendar Table with Power Query

In this step by step guide you'll learn how to create a Calendar Table using Power Query and save the code to reuse it later.

exceleratorbi.com.au

Thanks for your article. I will check it out. I need a lookup column for another spreadsheet I update yearly. The value to lookup is the String e.g. 1st Thu in Jan, etc.

 

[alansidman]

You could use Date.WeekOfMonth

 

[mib1019]

You could use Date.WeekOfMonth

Thanks for the help. It does not work, however. e.g. 1/28/24 is the 4th (Last) Sun in Jan, and Week of Month Returns 5.
Mary B.