Forum Discussion

yuval86's avatar
yuval86
Regular Visitor
9 years ago
Solved

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..:)

  • ImkeF's avatar
    ImkeF
    9 years ago

     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"

     

9 Replies

  • wgarn's avatar
    wgarn
    Advocate II
    9 years ago

    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's avatar
      MarcelBeug
      Community Champion
      9 years ago

      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. :smileyembarrassed:

    • maijanen's avatar
      maijanen
      Frequent Visitor
      6 years ago

      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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks, this is great and brief.

  • ImkeF's avatar
    ImkeF
    Community Champion
    9 years ago

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

    • yuval86's avatar
      yuval86
      Regular Visitor
      9 years ago

      Yes, one data per week- the first day.

       

      Thanks!

      • ImkeF's avatar
        ImkeF
        Community Champion
        9 years ago

         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"