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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
barakm
Frequent Visitor

Converting week and years in M code

Dear Team,I'm new to Power BI, trying to figure out the issue.

I have a column named "Period Week" with data looks like 23.2024  means the 23th week of the year 2024 

I wish to convert it a regular date in M code.

I tried chatGPT for help but I'm getting cyclic error each time   

maybe you can help ? what would be the right way to do this ?  

1 ACCEPTED SOLUTION
Ahmedx
Super User
Super User

and try this code in power query

 

[
    parts = Text.Split([Week Year], "."),
    weekNum = Number.FromText(parts{0}),
    yearNum = Number.FromText(parts{1}),
    startOfYear = #date(yearNum, 1, 1),
    dayOfWeek = Date.DayOfWeek(startOfYear, Day.Sunday),
    daysOffset = if dayOfWeek = 1 then 0 else 7 - dayOfWeek,
    firstSunday = Date.AddDays(startOfYear, daysOffset),
    weekStartDate = Date.AddDays(firstSunday, (weekNum - 1) * 7)
]
    [weekStartDate]

 

Screenshot_2.png

View solution in original post

4 REPLIES 4
barakm
Frequent Visitor

Thank you, for your help and time 

Ahmedx
Super User
Super User

and try this code in power query

 

[
    parts = Text.Split([Week Year], "."),
    weekNum = Number.FromText(parts{0}),
    yearNum = Number.FromText(parts{1}),
    startOfYear = #date(yearNum, 1, 1),
    dayOfWeek = Date.DayOfWeek(startOfYear, Day.Sunday),
    daysOffset = if dayOfWeek = 1 then 0 else 7 - dayOfWeek,
    firstSunday = Date.AddDays(startOfYear, daysOffset),
    weekStartDate = Date.AddDays(firstSunday, (weekNum - 1) * 7)
]
    [weekStartDate]

 

Screenshot_2.png

Ahmedx
Super User
Super User

try code in Dax

startweek date = 
VAR _s = SUBSTITUTE([Week Year],".","|")
var _year = PATHITEM(_s,2,INTEGER)
var _week =  PATHITEM(_s,1,INTEGER)
VAR _date = DATE(_year,1,-2)-WEEKDAY(DATE(_year,1,3))+_week*7
RETURN
_date

Screenshot_1.png

Anonymous
Not applicable

I would add a new query with a list of all of the dates from your first year (let's say 2021) to the most recent year, so = List.Dates(#date(2021, 1, 1), Duration.TotalDays(Date.From(DateTime.LocalNow())-#date(2021, 1, 1)), 1)

 

Use the GUI option "To Table", name that column "Dates", and then add a column using the function each Date.WeekOfYear([Dates])

If you want to, group by Week, and choose the Max aggregation for [Dates].
Now you can return to your original query, split your [Period Week] column by ".", and make the types "Int64.Type" (whole number). 
Now you can merge with your Dates query on Week Number and year. 

--Nate

 

 

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

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