Forum Discussion
How to create date from week and year colums in powerquery?
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
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:
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 WeekStartDateBelow 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"
8 Replies
- OwenAuger
Super User
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:
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 WeekStartDateBelow 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 differentDate.StartOfWeek(Date.AddWeeks(#date([Jaar], 1, 1), [Weeknummer] - 1), Day.Monday)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.
- v-csrikanth
Community Support
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 - 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
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.
