Hi There,
Below is my M language, can someone hep me create a new column called start date of the week?
I would like each week, the start date of the week is the Monday(The end date should be the Sunday).
Right now I have a column called week end date, but the end date is Saturday(Sun-Sat circle)
Please help me write a new column as the last step is OK.
let
//Build standard date columns.
Source = List.Dates,
CalendarStartDate = #date(2007, 1, 1), // Set the start date of the calendar as year, month, day
CalendarEndDate = Date.AddDays(Date.From(DateTime.FixedLocalNow()), 30), // Set the end data of the calendar. You can use DateTime.FixedLocalNow() plus/minus some number to base it on today's date
#"Invoked FunctionSource" = Source(CalendarStartDate, Duration.Days(CalendarEndDate - CalendarStartDate) + 1, #duration(1, 0, 0, 0)),
#"Table from List" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Rename Column to Date" = Table.RenameColumns(#"Table from List",{{"Column1", "Date"}}),
#"Changed Type to Date" = Table.TransformColumnTypes(#"Rename Column to Date",{{"Date", type date}}),
#"Added Year Column" = Table.AddColumn(#"Changed Type to Date", "Year", each Date.Year([Date])),
#"Added Month Number Column" = Table.AddColumn(#"Added Year Column", "Month Number", each Date.Month([Date])),
#"Added Day of Week Column" = Table.AddColumn(#"Added Month Number Column", "Day of Week", each Date.DayOfWeek([Date])+1),
#"Added Day of Month Column" = Table.AddColumn(#"Added Day of Week Column", "Day of Month", each Date.Day([Date])),
#"Added Day of Year Column" = Table.AddColumn(#"Added Day of Month Column", "Day of Year", each Date.DayOfYear([Date])),
#"Added Short Year Column" = Table.AddColumn(#"Added Day of Year Column", "Short Year", each Number.FromText(Text.End(Number.ToText([Year]),2))),
#"Added Week of Month Column" = Table.AddColumn(#"Added Short Year Column", "Week of Month", each Date.WeekOfMonth([Date])),
#"Added Week of Year Colum" = Table.AddColumn(#"Added Week of Month Column", "Week of Year", each Date.WeekOfYear([Date])),
#"Added Quarter of Year Column" = Table.AddColumn(#"Added Week of Year Colum", "Quarter of Year", each Date.QuarterOfYear([Date])),
#"Added YearMonth Integer Column" = Table.AddColumn(#"Added Quarter of Year Column", "YearMonth Integer", each Number.FromText(Text.Combine( {Number.ToText([Year]), Number.ToText([Month Number],"00")} ))),
#"Added YearQuarter Integer Column" = Table.AddColumn(#"Added YearMonth Integer Column", "YearQuarter Integer", each Number.FromText(Text.Combine( {Number.ToText([Year]), Number.ToText([Quarter of Year])} ))),
#"Added Month Name Column" = Table.AddColumn(#"Added YearQuarter Integer Column", "Month Name", each Date.ToText([Date],"MMM")),
#"Added Month Name Long Column" = Table.AddColumn(#"Added Month Name Column", "Month Name Long", each Date.ToText([Date],"MMMM")),
#"Added Day Name Column" = Table.AddColumn(#"Added Month Name Long Column", "Day Name", each Date.ToText([Date],"ddd")),
#"Added Day Name Long Column" = Table.AddColumn(#"Added Day Name Column", "Day Name Long", each Date.ToText([Date],"dddd")),
#"Added Month In Calendar Column" = Table.AddColumn(#"Added Day Name Long Column", "Month In Calendar", each Text.Combine( { [Month Name], " ", Number.ToText([Year]) })),
#"Added Quarter In Calendar Column" = Table.AddColumn(#"Added Month In Calendar Column", "Quarter in Calendar", each Text.Combine( { "Q", Number.ToText([Quarter of Year]), " ", Number.ToText([Year]) } )),
#"Added Date As Text Column" = Table.AddColumn(#"Added Quarter In Calendar Column", "Date As Text", each Date.ToText([Date],"MM/dd/yy")),
#"Added MonthYear Column" = Table.AddColumn(#"Added Date As Text Column", "MonthYear", each Date.ToText([Date],"MMM-yyyy")),
#"Added Week Ending Column" = Table.AddColumn(#"Added MonthYear Column","Week Ending", each Date.EndOfWeek([Date])),
//Add Month Start date to reference for the f_Referral measure.
AddMonthStart = Table.AddColumn(#"Added Week Ending Column", "Month Start", each Date.StartOfMonth([Date]), type date),
//Add Fiscal columns for fiscal years starting 4/1.
AddFiscalMonth = Table.AddColumn(AddMonthStart, "Fiscal Month Number", each if
[Month Number] >= 4 and [Month Number] <=12 then [Month Number] - 3 else if
[Month Number] = 3 then 12 else if
[Month Number] = 2 then 11 else if
[Month Number] = 1 then 10
else null, Int64.Type
),
AddFiscalQuarter = Table.AddColumn(AddFiscalMonth, "Fiscal Quarter", each if
[Fiscal Month Number] >= 1 and [Fiscal Month Number] <= 3 then 1 else if
[Fiscal Month Number] >= 4 and [Fiscal Month Number] <= 6 then 2 else if
[Fiscal Month Number] >= 7 and [Fiscal Month Number] <= 9 then 3 else if
[Fiscal Month Number] >= 10 and [Fiscal Month Number] <= 12 then 4
else null, Int64.Type
),
AddFiscalYear = Table.AddColumn(AddFiscalQuarter, "Fiscal Year", each if
[Month Number] >= 4 and [Month Number] <=12 then [Year] else if
[Month Number] >= 1 and [Month Number] <= 3 then [Year] - 1
else null, Int64.Type
),
#"Changed Column Types" = Table.TransformColumnTypes(AddFiscalYear,{{"Year", Int64.Type}, {"Month Number", Int64.Type}, {"Day of Week", Int64.Type}, {"Day of Month", Int64.Type}, {"Day of Year", Int64.Type}, {"Short Year", Int64.Type}, {"Week of Month", Int64.Type}, {"Week of Year", Int64.Type}, {"Quarter of Year", Int64.Type}, {"YearMonth Integer", Int64.Type}, {"YearQuarter Integer", Int64.Type}, {"Month Name", type text}, {"Month Name Long", type text}, {"Day Name", type text}, {"Day Name Long", type text}, {"Month In Calendar", type text}, {"Quarter in Calendar", type text}, {"Date As Text", type text}, {"MonthYear", type text}, {"Week Ending", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Column Types",{{"Date", Order.Ascending}}),
#"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Month In Calendar", "MONTH IN CALENDAR YEAR"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Day of Week", "Day of Week - Copy"),
#"Replaced Value" = Table.ReplaceValue(#"Duplicated Column",1,0,Replacer.ReplaceValue,{"Day of Week - Copy"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",2,1,Replacer.ReplaceValue,{"Day of Week - Copy"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",3,1,Replacer.ReplaceValue,{"Day of Week - Copy"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",4,1,Replacer.ReplaceValue,{"Day of Week - Copy"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3",5,1,Replacer.ReplaceValue,{"Day of Week - Copy"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4",6,1,Replacer.ReplaceValue,{"Day of Week - Copy"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5",7,0,Replacer.ReplaceValue,{"Day of Week - Copy"})
in
#"Replaced Value6"
