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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mb0307
Responsive Resident
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.

 

Thanks for your help in advance. 

1 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

 





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


View solution in original post

4 REPLIES 4
mahoneypat
Employee
Employee

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!

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


@mahoneypa HoosierBI on YouTube


mb0307
Responsive Resident
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

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

 





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


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

 

Jakinta_1-1628810800995.png

In Fridays step you can define time range and day of the week.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors