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....
  • 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"

     

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"

     

    • yuval86's avatar
      yuval86
      Regular Visitor
      9 years ago

      great, it was vary helpful.

    • RichXM655's avatar
      RichXM655
      Helper I
      2 years ago

      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: 

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