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/20201wk31/2021 shuld return 13/Aug/2021 I need this is Power Query only. Thanks for your help in advance.

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

Week/Year to Friday Date | Microsoft Fabric Community

4 Replies

mahoneypat
Microsoft Employee
5 years ago
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
  Responsive Resident
  5 years ago
  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
    Microsoft Employee
    5 years ago
    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

Forum Discussion

Week/Year to Friday Date