Forum Discussion
hoyt22
Helper I
Helper ICreating a date table with Broadcast Month and Year - Help with formulas
Hi all, I work in media, and we typically use broadcast weeks instead of calendar weeks. I'm creating a date table and managed to figure out the broadcast week formula, but broadcast month is pro...
Hi hoyt22 ,
audreygerred's insights were great.
Here's my solution for your reference. I continued on your code and got the following number of weeks.
Below is code you can refer to.
let
StartDate = #date(2024,1,1),
EndDate = #date(2024,12,31),
NumberOfDays = Duration.Days(EndDate - StartDate) + 1,
DateList = List.Dates(StartDate, NumberOfDays, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
#"Added Custom Columns" = Table.AddColumn(#"Converted to Table", "Calendar Year", each Date.Year([Date])),
#"Added Custom Columns1" = Table.AddColumn(#"Added Custom Columns", "Calendar Quarter", each "Qtr " & Number.ToText(Date.QuarterOfYear([Date]))),
#"Added Custom Columns2" = Table.AddColumn(#"Added Custom Columns1", "Calendar Month Number", each Date.Month([Date])),
#"Added Custom Columns3" = Table.AddColumn(#"Added Custom Columns2", "Calendar Month Name", each Date.ToText([Date], "MMMM")),
#"Added Custom Columns4" = Table.AddColumn(#"Added Custom Columns3", "Day", each Date.Day([Date])),
#"Added Custom Columns5" = Table.AddColumn(#"Added Custom Columns4", "Broadcast Week", each Date.StartOfWeek([Date], Day.Monday)),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom Columns5",{{"Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Broadcast Week"}, {{"allrows", each _, type table [Date=nullable date, Calendar Year=number, Calendar Quarter=text, Calendar Month Number=number, Calendar Month Name=text, Day=number, Broadcast Week=date]}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Week", each "Week"& Text.From([Index])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
#"Expanded allrows" = Table.ExpandTableColumn(#"Removed Columns", "allrows", {"Date", "Calendar Year", "Calendar Quarter", "Calendar Month Number", "Calendar Month Name", "Day"}, {"Date", "Calendar Year", "Calendar Quarter", "Calendar Month Number", "Calendar Month Name", "Day"})
in
#"Expanded allrows"
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hoyt22Helper I
Helper IThanks! I don't see Broadcast Month in your code, but I think I was able to figure it out. It seems to work even though I don't quite understand why yet.
I included the code below in case it helps anyone in the future:
let
StartDate = #date(2021, 12, 27),
EndDate = #date(2024, 12, 31),
NumberOfDays = Duration.Days(EndDate - StartDate) + 1,
DateList = List.Dates(StartDate, NumberOfDays, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
#"Added Custom Columns" = Table.AddColumn(#"Converted to Table", "Calendar Year", each Date.Year([Date])),
#"Added Custom Columns1" = Table.AddColumn(#"Added Custom Columns", "Calendar Quarter", each "Qtr " & Number.ToText(Date.QuarterOfYear([Date]))),
#"Added Custom Columns2" = Table.AddColumn(#"Added Custom Columns1", "Calendar Month Number", each Date.Month([Date])),
#"Added Custom Columns3" = Table.AddColumn(#"Added Custom Columns2", "Calendar Month", each Date.ToText([Date], "MMMM")),
#"Added Custom Columns4" = Table.AddColumn(#"Added Custom Columns3", "Calendar Day", each Date.Day([Date])),
#"Added Custom Columns5" = Table.AddColumn(#"Added Custom Columns4", "Broadcast Day", each Date.Day([Date])),
#"Added Custom Columns6" = Table.AddColumn(#"Added Custom Columns5", "Broadcast Week", each Date.StartOfWeek([Date], Day.Monday)),
#"Added Custom Columns7" = Table.AddColumn(#"Added Custom Columns6", "Broadcast Month",
each
let
// Start of the month for the current date
FirstOfNextMonth = Date.StartOfMonth(Date.AddMonths([Date], 1)),
// Monday of the week containing the 1st of the next month
FirstWeekOfNextMonth = Date.StartOfWeek(FirstOfNextMonth, Day.Monday)
in
if [Date] >= FirstWeekOfNextMonth
then Date.ToText(FirstOfNextMonth, "MMMM")
else Date.ToText(Date.StartOfMonth([Date]), "MMMM")
),
#"Added Custom Columns8" = Table.AddColumn(#"Added Custom Columns7", "Broadcast Month Number",
each List.PositionOf({"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}, [Broadcast Month]) + 1
),
#"Added Custom Columns9" = Table.AddColumn(#"Added Custom Columns8", "Broadcast Quarter",
each
if [Broadcast Month] = "January" or [Broadcast Month] = "February" or [Broadcast Month] = "March" then "Qtr 1"
else if [Broadcast Month] = "April" or [Broadcast Month] = "May" or [Broadcast Month] = "June" then "Qtr 2"
else if [Broadcast Month] = "July" or [Broadcast Month] = "August" or [Broadcast Month] = "September" then "Qtr 3"
else if [Broadcast Month] = "October" or [Broadcast Month] = "November" or [Broadcast Month] = "December" then "Qtr 4"
else null
),
#"Added Custom Columns10" = Table.AddColumn(#"Added Custom Columns9", "Broadcast Year",
each if [Date] >= Date.StartOfWeek(Date.AddDays(Date.StartOfMonth(Date.AddMonths([Date], 1)), -Date.DayOfWeek(Date.StartOfMonth(Date.AddMonths([Date], 1)), Day.Monday)), Day.Monday)
then Date.Year(Date.AddMonths([Date], 1))
else Date.Year([Date])
),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom Columns10",{{"Date", type date}, {"Broadcast Month Number", Int64.Type}, {"Calendar Month Number", Int64.Type}, {"Broadcast Week", type date}})
in
#"Changed Type"