Create new month column from mid month to mid month

[Popcorn101]

Hello! 

Need help from the experts over here. 
I am trying to create a histogram. right now I am stuck at creating a new "MONTH" column where my monthly range will be from previous 2nd sunday of month to the next 2nd sunday of the month.  
Please see image below. The date indicated is every sunday.  

 

Is there a formula I can to write when creating this new "Month" column?

Thank you!! 

[watkinnc]

I would do this in one step:

MonthName = Table.AddColumn(PriorStepOrTableName, "Month", each if Date.DayOfMonth([Cut-off Date]) = 14 then Date.MonthName(Cut-off Date]) else if Date.WeekOfMonth([Cut-Off Date], Day.Monday) > 2 then Date.MonthName(Date.AddDays(Date.EndOfMonth([Cut-off Date]), 1)) else Date.MonthName([Cut-Off Date]))

--Nate

[spinfuzer · Accepted solution]

Hi @Popcorn101 ,

You can list.generate a list of dates and identify which dates belong to which month.

Paste the query into a blank query and then merge it with your query.

let
    Source = 
    Table.FromValue(
        List.Generate(() =>
            [ 
                date = #date(2020,1,1), // start before a 2nd sunday so the query works properly
                sun_ct = Number.From(Date.DayOfWeek(date,Day.Sunday) = 0),
                group = Date.ToText(date, "MMM yyyy")
            ],
        each [date] <= #date(2026,12,31),  // change this to an end date that fits your entire population
        each
            [
                date = Date.AddDays([date],1),
                sun_ct = 
                    if Date.Month([date]) = Date.Month(date)
                    then if Date.DayOfWeek(date,Day.Sunday) = 0 then [sun_ct] + 1 else [sun_ct]
                    else if Date.DayOfWeek(date,Day.Sunday) <> 0 then 0 else 1, 
                group = if [sun_ct] <> 2 then [group] else Date.ToText(Date.AddMonths([date],1), "MMM yyyy")
            ], // pay attention to whether the variables have brackets or not. [date] means "PREVIOUS date" and date means the [date] + 1 in this case.
        each [
            date = [date],
            group = [group],
            sun_ct = if Date.DayOfWeek(date,Day.Sunday) = 0 then [sun_ct] else null   
            ] 

        )
    ),
    #"Expanded Value" = Table.ExpandRecordColumn(Source, "Value", {"date", "group", "sun_ct"}, {"date", "group", "sun_ct"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Value",{{"date", type date}})
in
    #"Changed Type"

[adudani]

hi @Popcorn101 ,

steps taken:

1. Created a calendar table.

2. Filtered the table to show only sundays.

3. Grouped rows by Month

4. Counted the number of sundays in the month.

5. Conditional Column -> If Sunday Count <= 2 return same month. else if 3, add 21 days to existing date and get the month else if 4 , add 14 days to existing date else if 5 add 7 days to existing date.

Copy and paste the code below into the advanced editor of a blank query. The steps from "FilteredRows_Only Sunday" is explained above.Prior to that is calendar table creation.

let

    StartDate = #date(2023, 1, 1),
    EndDate = Date.EndOfYear(DateTime.Date(DateTime.FixedLocalNow())) /*was "#date(2017, 1, 1)" Updated on 201802027: hard Coded End of Year caused some formulas to break, switching to dynamic date*/,
    ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type to Date" = Table.TransformColumnTypes(#"Renamed Columns as Date",{{"Date", type date}}),
    #"==Add Calendar Columns==" = #"Changed Type to Date",
    #"Added Calendar MonthNum" = Table.AddColumn(#"==Add Calendar Columns==", "MonthNum", each Date.Month([Date]), Int64.Type),
    #"Added Month Name" = Table.AddColumn(#"Added Calendar MonthNum", "Month", each Text.Start(Date.MonthName([Date]),3), type text),
    #"Added Month Name Long" = Table.AddColumn(#"Added Month Name", "MonthLong", each Date.MonthName([Date]), type text),
    #"Added Calendar Quarter" = Table.AddColumn(#"Added Month Name Long", "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date]))),
    #"Added Calendar Year" = Table.AddColumn(#"Added Calendar Quarter", "Year", each Date.Year([Date]), Int64.Type),
    #"Added MonthYearNum" = Table.AddColumn(#"Added Calendar Year" , "MonthYearNum", each [Year]*100 + [MonthNum] /*e.g. Sep-2016 would become 201609*/, Int64.Type),
    #"Added MonthYear" = Table.AddColumn(#"Added MonthYearNum", "MonthYear", each [Month] & "-" & Text.End(Text.From([Year]),2)),
    #"Added MonthYearLong" = Table.AddColumn(#"Added MonthYear", "MonthYearLong", each [Month] & "-" & Text.From([Year])),
    #"Added WeekdayNum" = Table.AddColumn(#"Added MonthYearLong", "WeekdayNum", each Date.DayOfWeek([Date]), Int64.Type),
    #"Added Weekday Name" = Table.AddColumn(#"Added WeekdayNum", "Weekday", each Text.Start(Date.DayOfWeekName([Date]),3), type text),
    #"Added WeekdayWeekend" = Table.AddColumn(#"Added Weekday Name", "WeekdayWeekend", each if [WeekdayNum] = 0 or [WeekdayNum] = 6
then "Weekend"
else "Weekday"),
    #"Filtered Rows Sundays Only (Start of Week)" = Table.SelectRows(#"Added WeekdayWeekend", each ([WeekdayNum] = 0)),
    #"Inserted Start of Week (WeekDate)" = Table.AddColumn(#"Filtered Rows Sundays Only (Start of Week)", "WeekDate", each Date.StartOfWeek([Date]), type date),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Start of Week (WeekDate)", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    
    //Create new month column from mid month to mid month
    #"Filtered Rows_Only Sundays" = Table.SelectRows(#"Inserted Week of Year", each ([Weekday] = "Sun")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows_Only Sundays", {"Month"}, {{"Total # Sundays", each Table.RowCount(_), Int64.Type}, {"Data", each _, type table [Date=nullable date, MonthNum=number, Month=text, MonthLong=text, Quarter=text, Year=number, MonthYearNum=number, MonthYear=text, MonthYearLong=text, WeekdayNum=number, Weekday=text, WeekdayWeekend=text, WeekDate=date, Week of Year=number]}}),
    #"Sunday Counter" = Table.AddColumn(#"Grouped Rows", "Sunday Counter", each Table.AddIndexColumn([Data],"Sunday Counter",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Sunday Counter",{"Sunday Counter"}),
    #"Expanded Sunday Counter" = Table.ExpandTableColumn(#"Removed Other Columns", "Sunday Counter", {"Date", "MonthNum", "Month", "MonthLong", "Quarter", "Year", "MonthYearNum", "MonthYear", "MonthYearLong", "WeekdayNum", "Weekday", "WeekdayWeekend", "WeekDate", "Week of Year", "Sunday Counter"}, {"Date", "MonthNum", "Month", "MonthLong", "Quarter", "Year", "MonthYearNum", "MonthYear", "MonthYearLong", "WeekdayNum", "Weekday", "WeekdayWeekend", "WeekDate", "Week of Year", "Sunday Counter.1"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Sunday Counter",{{"Date", type date}, {"MonthNum", Int64.Type}, {"Month", type text}, {"MonthLong", type text}, {"Quarter", type text}, {"Year", Int64.Type}, {"MonthYearNum", Int64.Type}, {"MonthYear", type date}, {"MonthYearLong", type date}, {"WeekdayNum", Int64.Type}, {"Weekday", type text}, {"WeekdayWeekend", type text}, {"WeekDate", type date}, {"Week of Year", Int64.Type}, {"Sunday Counter.1", Int64.Type}}),
    #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month.1", each Date.Month([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
    #"Required Month" = Table.AddColumn(#"Inserted Month Name", "Required Month", each if  [Sunday Counter.1]  <= 2 then Date.Month([Date]) 
else if 
[Sunday Counter.1]  =3  then Number.From(Date.Month(Date.AddDays([Date],21)))
else if 
[Sunday Counter.1]  =4  then Number.From(Date.Month(Date.AddDays([Date],14)))
else Number.From(Date.Month(Date.AddDays([Date],7)))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Required Month",{{"Required Month", Int64.Type}}),
    #"Required MonthName" = Table.AddColumn(#"Changed Type1", "Required MonthName", each if [Required Month] = 1 then "Jan" else if [Required Month] = 2 then "Feb" else if [Required Month] = 3 then "Mar" else if [Required Month] = 4 then "Apr" else if [Required Month] = 5 then "May" else if [Required Month] = 6 then "Jun" else if [Required Month] = 7 then "Jul" else if [Required Month] = 8 then "Aug" else if [Required Month] = 9 then "Sep" else if [Required Month] = 10 then "Oct" else if [Required Month] = 11 then "Nov" else if [Required Month] = 12 then "Dec" else 0)
in
    #"Required MonthName"

I mapped the last two columns "Required Month" and "Required Moth Name" to the input you provided which gives the below output: