Week/Year to Friday Date

[mb0307]

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. 

[mahoneypat]

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

[mb0307]

@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

[mahoneypat · Accepted 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

[Jakinta]

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.