none
Power Query - Create a list of Months RRS feed

  • Question

  • Hi All,

    I need to create a date dimension where the lowest level is month. I've seen examples which use the list function such as

        Source = List.Dates(#date(2000, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2000,1,1)), #duration(1,0,0,0)),

    The above increments by 1 day which is defined in the 1st argument of the #duration. My question is how can I dynamically change the value of this 1st argument such that its the number of days in the current month hence it will increment to only return the 1st date in the Month e.g

    1/1/2000

    1/2/2000

    1/3/2000

    etc..

    I prefer to use an elegant approach if possible, the alternative would be return all dates, create a custom column from these dates which returns the month date - delete the dates column - get a distinct list of the month dates

    Thank you for your help

    John

    Tuesday, July 21, 2015 10:11 PM

Answers

  • Hi John,

    this is an alternative - although don't know if it matches your criteria :-)

    let
        Years = Table.FromList({2012..2015}, Splitter.SplitByNothing(), null,null, ExtraValues.Error),
        AddMonth = Table.AddColumn(Years, "Month", each {1..12}),
        ShowMonths = Table.ExpandListColumn(AddMonth, "Month"),
        CreateDate = Table.AddColumn(ShowMonths, "Date", each #date([Column1],[Month],1))
    in
        CreateDate

    As the biggest granularity of duration is day, I cannot think of an elegant way to roll up on month level.


    Imke

    • Marked as answer by Shiangoli Wednesday, July 22, 2015 9:26 AM
    Wednesday, July 22, 2015 6:52 AM
    Moderator

All replies

  • Hi John,

    this is an alternative - although don't know if it matches your criteria :-)

    let
        Years = Table.FromList({2012..2015}, Splitter.SplitByNothing(), null,null, ExtraValues.Error),
        AddMonth = Table.AddColumn(Years, "Month", each {1..12}),
        ShowMonths = Table.ExpandListColumn(AddMonth, "Month"),
        CreateDate = Table.AddColumn(ShowMonths, "Date", each #date([Column1],[Month],1))
    in
        CreateDate

    As the biggest granularity of duration is day, I cannot think of an elegant way to roll up on month level.


    Imke

    • Marked as answer by Shiangoli Wednesday, July 22, 2015 9:26 AM
    Wednesday, July 22, 2015 6:52 AM
    Moderator
  • You could also just subscribe to Boyan Penev's DateStream feed:

    https://datamarket.azure.com/dataset/boyanpenev/datestream

    Wednesday, July 22, 2015 12:32 PM
  • Maybe not exactly what you need, but this an elegant way tho producte a list of monthnames. By altering the format specifier you can alter the ouput to your needs.

    let
        Monthname = List.Generate(()=> [ x = 1 , y = {}] , each [x] <= 12 , each [x = [x]+1] , each Date.ToText(#date(2000,[x],1),"MMMM"))
    in
        Monthname

    Don use the year 0 in the #date, it will produce an error

    Tuesday, October 16, 2018 4:03 PM
  • I think pretty simple solution is to use List.Generate:

    StartDate = #date(2010, 1,1),
    EndDate = #date(2020, 13,5),
    ListOfMonths = List.Generate(()=>StartDate, each _ <= EndDate, each Date.AddMonths(_, 1))
    

    Vaclav

    Tuesday, January 14, 2020 10:14 AM