Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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"
Solved! Go to Solution.
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"
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"
You can add the optional week start parameter to your Date.WeekOfYear, like
Date.WeekOfYear([Date], Day.Monday)
--Nate
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
67 | |
61 | |
23 | |
17 | |
12 |