Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Vijay_A_Verma
Super User
‎06-06-2022 07:44 AM

First Mon, Tue, Wed, Thu, Fri, Sat and Sun of the Month in Power Query Language M

Use Case - There are many business scenarios where you would be asked to find first Monday, Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday of the Month. For example, monthly steering committee meeting will be held on first Thursday of the month every month. Hence, if a date is given, following should be the answer

FirstDayofMonth.png

Solution - Following formulas can be used to calculate these dates

 

First Monday of the Month
= Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),1)

First Tuesdday of the Month
Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),2)

First Wednesday of the Month
Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),3)

First Thursday of the Month
Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),4)

First Friday of the Month
Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),5)

First Saturday of the Month
Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),6)

First Sunday of the Month
Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),0)

 

Note - 1, 2....0 can also be replaced with Day.Monday, Day.Tuesday.......Day.Sunday. Hence, for Monday it can be written as

 

= Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),Day.Monday)

 

But this is possible only if you follow English language. Hence, it is better to use 1,2.....0 not Day.Monday, Day.Tuesday.....Day.Sunday

Following Query M code can be used to test above

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSN9U3MjAyVIrVAfL0gXwgzwjMAzJNETxjfUMEx0TfyALBM9U3Q3DMUMww1zeCaYsFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    FirstMonday = Table.AddColumn(#"Changed Type", "Monday", each Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),1), type date),
    FirstTuesday = Table.AddColumn(FirstMonday, "Tuesday", each Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),2), type date),
    FirstWednesday = Table.AddColumn(FirstTuesday, "Wednesday", each Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),3), type date),
    FirstThursday = Table.AddColumn(FirstWednesday, "Thursday", each Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),4), type date),
    FirstFriday = Table.AddColumn(FirstThursday, "Friday", each Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),5), type date),
    FirstSaturday = Table.AddColumn(FirstFriday, "Saturday", each Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),6), type date),
    FirstSunday = Table.AddColumn(FirstSaturday, "Sunday", each Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),0), type date)
in
    FirstSunday

 

--- End of Article ---
0