Forum Discussion
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"
- Anonymous2 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
- AnonymousNot applicable
You can add the optional week start parameter to your Date.WeekOfYear, like
Date.WeekOfYear([Date], Day.Monday)
--Nate
- dufoq3Community 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"