Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## How to Convert Nth Day of a Month to Actual Date

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.

1 ACCEPTED SOLUTION
Super User

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")``````

2 REPLIES 2
Microsoft Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

Super User

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")``````

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors