cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yuval86 Frequent Visitor
Frequent 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

Accepted Solutions
ImkeF
MVP

Re: convert week number into data

 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"

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

6 REPLIES 6
ImkeF
MVP

Re: convert week number into data

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




yuval86 Frequent Visitor
Frequent Visitor

Re: convert week number into data

Yes, one data per week- the first day.

 

Thanks!

ImkeF
MVP

Re: convert week number into data

 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"

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

yuval86 Frequent Visitor
Frequent Visitor

Re: convert week number into data

great, it was vary helpful.

wgarn Frequent Visitor
Frequent Visitor

Re: convert week number into data

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.

MarcelBeug Super Contributor
Super Contributor

Re: convert week number into data

@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)

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,812)