Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have Week/Year in a column and would like to convert into Friday of the week.
Example:
wk31/2021 should return 06/Aug/20201
wk31/2021 shuld return 13/Aug/2021
I need this is Power Query only.
Thanks for your help in advance.
Solved! Go to Solution.
You can also add a custom column with this formula in the pop-up box.
= let
weekno = Number.FromText(Text.BeforeDelimiter([#"Week/Year"], "/")),
yearno = Number.FromText(Text.AfterDelimiter([#"Week/Year"], "/"))
in
Date.EndOfWeek(Date.AddWeeks(Date.StartOfWeek(#date(yearno,1,1), Day.Saturday), weekno), Day.Saturday)
You can adjust this part to refine the starting point.
Date.StartOfWeek(#date(yearno,1,1), Day.Saturday)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
I believe those two dates are Week 32 and 33, but you can use this expression in a custom column to get that.
= Date.EndOfWeek([Date], Day.Saturday)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat Thanks. i have updated my query to avoid confusion.
31 and 32 are week numbers with year. I need Friday date for each week please
Thanks
You can also add a custom column with this formula in the pop-up box.
= let
weekno = Number.FromText(Text.BeforeDelimiter([#"Week/Year"], "/")),
yearno = Number.FromText(Text.AfterDelimiter([#"Week/Year"], "/"))
in
Date.EndOfWeek(Date.AddWeeks(Date.StartOfWeek(#date(yearno,1,1), Day.Saturday), weekno), Day.Saturday)
You can adjust this part to refine the starting point.
Date.StartOfWeek(#date(yearno,1,1), Day.Saturday)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here is one of many possible ways to do it...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjbUNzIwMlSK1QGyjUBsIzDbEIltZAplxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Week/Year" = _t]),
Fridays = List.Transform( List.Select({Number.From(#date(2021,1,1))..Number.From(#date(2022,12,31))}, each Date.DayOfWeek(Date.From(_)) = 5), Date.From ),
WeekYears = List.Transform(Fridays, each Text.From(Date.WeekOfYear(_)) & "/" & Text.From(Date.Year(_)) ),
FINAL = Table.AddColumn(Source, "FridayDate", each Fridays {List.PositionOf(WeekYears, [#"Week/Year"])}, type date)
in
FINAL
In Fridays step you can define time range and day of the week.