Forum Discussion

IF's avatar
IF
Post Prodigy
2 years ago
Solved

Power query week number

Hi,

In power query i have a date table. I want to exract the number of the week. However it starts the week on Sunday. How can i change it and start the week from Monday. For example, I want to see 7 January 2024 in week 1 and day number should be 7.

 

Thanks in advance

 

Date table is:

let
Source = List.Dates(#date(2020,01,01), 365*7, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Column1]), Int64.Type),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Year", "Week of Year", each Date.WeekOfYear([Column1]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Week of Year", "Day of Week", each Date.DayOfWeek([Column1]), Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Day of Week",{{"Column1", "Date"}}),
#"Inserted Start of Week" = Table.AddColumn(#"Renamed Columns", "Start of Week", each Date.StartOfWeek([Date]), type date),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Start of Week",{"Start of Week"})
in
#"Removed Columns"

  • Anonymous's avatar
    Anonymous
    2 years ago

    You can add the optional week start parameter to your Date.WeekOfYear, like

     

    Date.WeekOfYear([Date], Day.Monday)

     

    --Nate

  • Hi IF,

     

    let
        Source = #table(type table[Date=date], {{#date(2024,1,7)}}),
        #"Inserted Week of Year" = Table.AddColumn(Source, "Week of Year", each Date.WeekOfYear([Date], Day.Monday), Int64.Type),
        #"Inserted Day of Week" = Table.AddColumn(#"Inserted Week of Year", "Day of Week", each Date.DayOfWeek([Date], Day.Monday) +1, Int64.Type)
    in
        #"Inserted Day of Week"

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    You can add the optional week start parameter to your Date.WeekOfYear, like

     

    Date.WeekOfYear([Date], Day.Monday)

     

    --Nate

  • dufoq3's avatar
    dufoq3
    Community Champion

    Hi IF,

     

    let
        Source = #table(type table[Date=date], {{#date(2024,1,7)}}),
        #"Inserted Week of Year" = Table.AddColumn(Source, "Week of Year", each Date.WeekOfYear([Date], Day.Monday), Int64.Type),
        #"Inserted Day of Week" = Table.AddColumn(#"Inserted Week of Year", "Day of Week", each Date.DayOfWeek([Date], Day.Monday) +1, Int64.Type)
    in
        #"Inserted Day of Week"