cancel
Showing results for
Did you mean:
Responsive Resident

## Week/Year to Friday Date

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.

1 ACCEPTED SOLUTION
Super User

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

You can adjust this part to refine the starting point.

Date.StartOfWeek(#date(yearno,1,1), Day.Saturday)

Pat

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

4 REPLIES 4
Super User

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

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

Responsive Resident

@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

Super User

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

You can adjust this part to refine the starting point.

Date.StartOfWeek(#date(yearno,1,1), Day.Saturday)

Pat

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

Super User

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.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!