How to Convert Nth Day of a Month to Actual Date

[Anonymous]

How to translate this text (3rd Wednesday) for instance to an actual date of a given month? I wish to apply this thru transformation not by adding a new column, if possible of course, thank you.

[mahoneypat]

The solution from @wdx223_Daniel  is very good, but this one was interesting so I tried it too. Here's another way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlLSUTIEYuOiFAXf/LyUxEqlWB24DBjnpSi4FWWCZWIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t, Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", Int64.Type}, {"Text", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each let
num = Number.FromText(Text.Start([Text],1)),
day = Text.AfterDelimiter([Text], " "),
year = [Year],
month = [Month],
eom = Date.EndOfMonth(#date(year, month, 1)),
dates = List.Transform({1..Date.Day(eom)}, each #date(year, month, _) ),
seldates = List.Select(dates, each Date.DayOfWeekName(_) = day),
result = seldates{num-1}
in  
result, type date)
in
    #"Added Custom"

Pat

[wdx223_Daniel · Accepted solution]

try this custom function code

let
    fx=(w,optional m,optional y)=>
        let a=Text.Split(w," "),
            idx=List.PositionOf({"1st","2nd","3rd","4th","5th"},a{0}),
            day=List.PositionOf({"mon","tue","wed","thu","fri","sat","sun"},
                                Text.Lower(Text.Start(a{1},3))
                            ),
            month=if m is number then Number.Mod(m-1,12)
                else List.PositionOf({"jan","feb","mar","apr","may","jun","jul","aug","sep","nov","dec"},
                                    Text.Start(Text.Lower(if m=null or m="" or m=0 then DateTime.ToText(DateTime.LocalNow(),"MMM","en") else m),3)
                                    )+1,
            year=(if y=null or y="" or y=0 then Date.Year(DateTime.LocalNow()) else if Text.Length(Text.From(y))<4 then Number.From(Text.Start(DateTime.ToText(DateTime.LocalNow(),"yyyy"),2)&Text.End(Text.From(y),2)) else Number.From(y))+(if m is number then Number.IntegerDivide(m,12) else 0),
            date1=#date(year,month,1),
            weekday=Date.DayOfWeek(date1,Day.Monday)
        in if idx=-1 or day=-1 or month=-1 then "there is not such a date!" else Date.AddDays(date1,day-weekday+Byte.From(weekday>day)*7+idx*7)
in fx("3rd Tuesday")