Forum Discussion

eggwald's avatar
eggwald
Frequent Visitor
2 years ago
Solved

Month List Table

Hello,    I am really struggling on this. It seems like it should be easy, but I think I am missing something! 🙂   I need a table with a list of months starting April 2020 to today and have it a...
Need Help
  • BA_Pete's avatar
    BA_Pete
    2 years ago

    Hi Ed,

     

    Try this Power Query solution:

    let
    Source =
    List.Distinct(
        List.Transform(
            { Number.From(#date(2020,4,1))..Number.From(Date.From(DateTime.LocalNow())) },
            each Text.Combine(
                {
                   Text.Start(Date.MonthName(Date.From(_)), 3),
                   Text.End(Text.From(Date.Year(Date.From(_))), 2)
                }, "-"
            )
        )
    ),
    convertToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    convertToTable

     

    For this output:

     

    Pete

  • eggwald's avatar
    eggwald
    2 years ago

    months = var t1 = CALENDAR(DATE(2020, 4, 1), TODAY()) var t2 = ADDCOLUMNS(t1, "time_y", format([Date], "yyyy"), "time_ym", format([Date], "yyyy-MM") ) var t3 = GROUPBY(t2, [time_y], [time_ym], "d1", minx(CURRENTGROUP(), [Date]), "d2", maxx(CURRENTGROUP(), [Date])) return t3

BA_Pete's avatar
BA_Pete
Super User
2 years ago

Hi Ed,

 

Try this Power Query solution:

let
    Source =
    List.Distinct(
        List.Transform(
            { Number.From(#date(2020,4,1))..Number.From(Date.From(DateTime.LocalNow())) },
            each Text.Combine(
                {
                   Text.Start(Date.MonthName(Date.From(_)), 3),
                   Text.End(Text.From(Date.Year(Date.From(_))), 2)
                }, "-"
            )
        )
    ),
    convertToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    convertToTable

 

For this output:

 

Pete

eggwald's avatar
eggwald
Frequent Visitor
2 years ago

months = var t1 = CALENDAR(DATE(2020, 4, 1), TODAY()) var t2 = ADDCOLUMNS(t1, "time_y", format([Date], "yyyy"), "time_ym", format([Date], "yyyy-MM") ) var t3 = GROUPBY(t2, [time_y], [time_ym], "d1", minx(CURRENTGROUP(), [Date]), "d2", maxx(CURRENTGROUP(), [Date])) return t3