Power query week number

[IF]

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"

[watkinnc · Accepted solution]

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

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

--Nate

[dufoq3 · Accepted solution]

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"

[watkinnc · Accepted solution]

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

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

--Nate