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.
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.
Solved! Go to 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")
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.