How to create date from week and year colums in powerquery?

[sylvianelissen]

Hi,

I have data with a weeknumber column and a year column. How can I add an extra column that gives me the date of the first day of that week?

Attached a print screen of my data.

Thanks for your help!

Regards

Sylvia

[v-csrikanth]

Hi @sylvianelissen 
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @sylvianelissen 
I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @sylvianelissen 
As highlighted by @Fowmy @OwenAuger , the proposed approach appears to effectively address your requirements. Please accept the response which resolve your issue.

Thanks,
Cheri Srikanth

[OwenAuger · Accepted solution]

Hi @sylvianelissen,

I realise @Fowmy just replied, but just adding an option I had been looking at.

If you are using ISO Weeks, you would need to implement something like this:

https://www.ablebits.com/office-addins-blog/excel-weeknum-function-convert-week-number-date/#convert...

In Power Query, you could create a function to do the conversion:

//fn_YearWeeknumToWeekStartDate
(Year as number, #"Week Number" as number) as date =>
  let
    Jan01           = #date(Year, 1, 1),
    Jan03           = #date(Year, 1, 3),
    Jan03_DayOfWeek = Date.DayOfWeek(Jan03, Day.Sunday),
    WeekStartDate   = Date.AddDays(Jan01, - Jan03_DayOfWeek + #"Week Number" * 7 - 4)
  in
    WeekStartDate

Below is a sample query to test the above function:

let
  Source = #table(
    type table [Jaar = Int64.Type, Weeknummer = Int64.Type],
    {{2025, 1}, {2025, 2}, {2025, 3}, {2025, 4}}
  ),
  #"Added Week Start Date" = Table.AddColumn(
    Source,
    "Week Start Date",
    each fn_YearWeeknumToWeekStartDate([Jaar], [Weeknummer]),
    type date
  )
in
  #"Added Week Start Date"

[Fowmy]

@sylvianelissen 

Add a custom column: I assumed week starts on Monday. you may change it if it's different

Date.StartOfWeek(Date.AddWeeks(#date([Jaar], 1, 1), [Weeknummer] - 1), Day.Monday)

[sylvianelissen]

Thanks for your reply, but your code gives an error....any suggestions? See print screen 

[Fowmy]

@sylvianelissen 

Sorry, I can't see the exact error message from here. Could you verify that the year and week number are correct and thier data types are numbers? Click on any cell showing "Error," check the detailed error message below, and share a screenshot to help diagnose the issue.

[sylvianelissen]

Thanks...but it works! It was the number format!