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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
yuval86
Regular Visitor

convert week number into data

Hello,

 

I have a week number (1,2,...,52) and I have the year, I'm looking for a function that could transform\convert the week and the year into date.

for some reason I didn't manage to do that.

 

Tank you..:)

1 ACCEPTED SOLUTION

 As I'm not aware of a function that returns the date of the week, instead I'd create a calendar for the year (replace "YourYear" respectively), create the week numbers and their first dates. Then filter on your week-numbers:

 

let
    Source = {Number.From(#date(YourYear,01,01))..Number.From(#date(YourYear,12,31))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    WeekColumn = Table.AddColumn(#"Changed Type", "Week", each Date.WeekOfYear([Column1])),
    StartOfWeek = Table.AddColumn(WeekColumn, "StartOfWeek", each Date.StartOfWeek([Column1])),
    #"Filtered Rows1" = Table.SelectRows(StartOfWeek, each [Week] >= List.Min(YourWeekList) and [Week] <= List.Min(YourWeekList))
in
    #"Filtered Rows1"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

9 REPLIES 9
wgarn
Advocate II
Advocate II

Another way is to insert a new new column (Power BI desktop >> New Measure >> New Column)

ndate = DATE([year],1,-2)-WEEKDAY(DATE([year],1,3))+[week]*7

 This is based on the ISO week date, which means we need to find the Monday nearest to the 1st of January.

Anonymous
Not applicable

Thanks, this is great and brief.

Hello @wgarn ! This would actually solve the issue I have had but the code you had there returns Expression.Error DATE was not recocnized. Do you know the reason? Same actually comes with WEEKDAY.

MarcelBeug
Community Champion
Community Champion

@wgarn ISO Week Number 1 is the week (Mo-Su) that contains the 4th of January.

For the correct rules check (the comments below) my video.

 

Edit: ah, you mean Monday closest to January 1st is the start of week 1?

That looks like another correct way of formulating ISO week 1. Smiley Embarassed

Specializing in Power Query Formula Language (M)
ImkeF
Super User
Super User

Do you expect to create one date per week (if yes: which one? First, last?) or all days?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Yes, one data per week- the first day.

 

Thanks!

 As I'm not aware of a function that returns the date of the week, instead I'd create a calendar for the year (replace "YourYear" respectively), create the week numbers and their first dates. Then filter on your week-numbers:

 

let
    Source = {Number.From(#date(YourYear,01,01))..Number.From(#date(YourYear,12,31))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    WeekColumn = Table.AddColumn(#"Changed Type", "Week", each Date.WeekOfYear([Column1])),
    StartOfWeek = Table.AddColumn(WeekColumn, "StartOfWeek", each Date.StartOfWeek([Column1])),
    #"Filtered Rows1" = Table.SelectRows(StartOfWeek, each [Week] >= List.Min(YourWeekList) and [Week] <= List.Min(YourWeekList))
in
    #"Filtered Rows1"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Apologies for resurrecting an old thread, but i've implemented this solution and it's hit a snag - The code generates a column of tables as it should but when I then try and insert a step after it to expand out the "startofweek" column, I get an error: 

RichXM655_0-1708942956129.png

"gaweek" is the name of the "week" column in my source data which is drawn from Google Analytics (which I replaced the "yourweeklist" tags with in the supplied code as I think I was supposed to.)

great, it was vary helpful.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.