Generate a table of yearMonth info & names. Handy for Power BI + Google Analytics.

yearMonth-index-table.powerquery

let
    yearsList = {2012..2099},

    months = {1..12},

    CalcMonthOffset = (dateA as date, dateB as date) as number => let
        ordA = Date.Year(dateA) * 12 + Date.Month(dateA),
        ordB = Date.Year(dateB) * 12 + Date.Month(dateB),
        result = ordA - ordB
    in
        result,

    yearMonth = (year as number, month as number) as text => let
        result = Number.ToText(year) & Text.PadStart(
            Number.ToText(month),
            2,
            "0"
        )
    in
        result,

    yearToMonths = (year as number) as list => let
        result = List.Transform(
            months,
            each yearMonth(year, _)
        )
    in
        result,

    yearsWithMonths = List.Combine(
        List.Transform(
            yearsList,
            each yearToMonths(_)
        )
    ),

    // exclude first 4 months of 2012
    tailYearsWithMonths = List.Range(
        yearsWithMonths,
        4,
        999
    ),

    ConvertedToTable = Table.FromList(
        tailYearsWithMonths,
        Splitter.SplitByNothing(),
        {"monthYear Index"},
        null,
        ExtraValues.Error
    ),

    keySet = Table.AddKey(
        ConvertedToTable,
        {"monthYear Index"},
        true
    ),

    withDateText = Table.AddColumn(
        keySet,
        "Date",
        each Date.FromText([#"monthYear Index"] & "01")
    ),

    withDate = Table.TransformColumnTypes(
        withDateText,
        {{"Date", type date}}
    ),

    withYears = Table.AddColumn(
        withDate,
        "Year",
        each Text.Start([#"monthYear Index"], 4)
    ),

    withMonthOffsetText = Table.AddColumn(
        withYears,
        "Month Offset",
        each CalcMonthOffset(
            [#"Date"],
            DateTime.Date(DateTime.LocalNow())
        )
    ),

    withMonthOffset = Table.TransformColumnTypes(
        withMonthOffsetText,
        {{"Month Offset", Int64.Type}}
    ),

    sansFutureMonths = Table.SelectRows(
        withMonthOffset,
        each [#"Month Offset"] < 1
    ),

    withMonthNumbers = Table.AddColumn(
        sansFutureMonths,
        "Month Number",
        each Text.Range(
            [#"monthYear Index"],
            4,
            2
        )
    ),

    withShortMonthNames = Table.AddColumn(
        withMonthNumbers,
        "Month Abbreviation",
        each Date.ToText([#"Date"], "MMM")
    ),

    withLongMonthNames = Table.AddColumn(
        withShortMonthNames,
        "Month Name",
        each Date.ToText([#"Date"], "MMMM")
    ),

    withLongMonthAndYear = Table.AddColumn(
        withLongMonthNames,
        "Month Name and Year",
        each Date.ToText([#"Date"], "MMMM yyyy")
    ),

    withSortableLongMonthAndYear = Table.AddColumn(
        withLongMonthAndYear,
        "Sortable Month Name and Year",
        each Date.ToText([#"Date"], "yyyy-MM MMMM")
    )

in
    withSortableLongMonthAndYear