locked
Add column with list containing months RRS feed

  • Question

  • Dear all, 

    If I have a start date and end date in a table I am able to add a list to that containing a table with all dates in between:

    { Number.From([EventDate])..Number.From([EndDate]) }

    However would it be possible to create months of that list instead of days?

    Many thanks,

    Regards, 

    Michiel

    Monday, February 25, 2019 6:13 AM

Answers

  • I copypasted a portion of my script that should be useful

        mindate= List.Min(extractDate[SourceFileDate]),
        maxdate= List.Max(extractDate[SourceFileDate]),
        yearcol = List.Numbers(Number.FromText(Text.Start(mindate,4))
    ,Number.FromText(Text.Start(maxdate,4))+1-Number.FromText(Text.Start(mindate,4))),
        #"Converted to Table" = Table.FromList(yearcol, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Added Custom" = Table.AddColumn(#"Converted to Table", "months", each {"01","02","03","04","05","06","07","08","09","10","11","12"}),
        #"Expanded months" = Table.ExpandListColumn(#"Added Custom", "months"),
        #"Inserted Merged Column" = Table.AddColumn(#"Expanded months", "periodSort", each Text.Combine({Text.From([Column1], "en-US"), [months]}, ""), type text),
        #"Filtered Rows1" = Table.SelectRows(#"Inserted Merged Column", each ([periodSort] >= mindate and [periodSort] < maxdate )),
        #"Added Custom1" = Table.AddColumn(#"Filtered Rows1", "period", each Text.From([Column1])&" "&Text.Start(Date.MonthName(#date(2000,Number.FromText([months]),01)),3)),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Column1", "months"})

    With this, I create a field with format YYYYMM (201812) and one with format YYYY MMM (2018 dec)

    Depending on how you want it, you should change the script but the idea is the same

    • Marked as answer by Michiel Soede Monday, February 25, 2019 3:07 PM
    Monday, February 25, 2019 1:16 PM
  • You can easily use Date.ToText to personalize the date you want to see:

    = List.Transform(
        List.Distinct(
            List.Transform(
                {Number.From(#datetime(2019, 1, 4, 0, 0, 0))..Number.From(#datetime(2020, 4, 24, 0, 0, 0))}, 
                each Date.StartOfMonth(Date.From(_)) 
            ) 
        ),
        each Date.ToText(_, "MMMM yyyy")
    )

    • Edited by anthony34 Monday, February 25, 2019 2:37 PM
    • Marked as answer by Michiel Soede Monday, February 25, 2019 6:51 PM
    Monday, February 25, 2019 2:13 PM
  • Ah yes, my SourceFileDate field is on the form YYYYMM like 201610 with the type "any". 

    Text.Start(maxdate,4) takes the first 4 characters of the field, which would be 2016. Using Text.End(maxdate,2) you can also get the last two characters, which would be 10 month. These functions need type "text"  


    • Marked as answer by Michiel Soede Sunday, March 3, 2019 2:25 PM
    Tuesday, February 26, 2019 8:22 AM

All replies

  • I am not sure how you want to deal the years, and also if you you want months as "January" or as number (1). All depends what you are up to. Here some tips:

    You could try this:

    List.Distinct( List.Transform({Number.From(#datetime(2019, 1, 4, 0, 0, 0))..Number.From(#datetime(2019, 4, 24, 0, 0, 0))}, each Date.MonthName(Date.From(_)) ) )

    or

    List.Distinct( List.Transform({Number.From(#datetime(2019, 1, 4, 0, 0, 0))..Number.From(#datetime(2019, 4, 24, 0, 0, 0))}, each Date.Month(Date.From(_)) ) )

    Maybe a better approach that would keep the duplicated months over different years could be:

    List.Transform(
        List.Distinct(
            List.Transform(
                {Number.From(#datetime(2019, 1, 4, 0, 0, 0))..Number.From(#datetime(2020, 4, 24, 0, 0, 0))},
                each Date.StartOfMonth(Date.From(_))
            )
        ),
        each Date.MonthName(_)
    ),

    Best, Anthony

    • Edited by anthony34 Monday, February 25, 2019 7:22 AM
    Monday, February 25, 2019 7:07 AM
  • Hi Anthony, 

    Thats a great solution:

    List.Transform(
        List.Distinct(
            List.Transform(
                {Number.From(#datetime(2019, 1, 4, 0, 0, 0))..Number.From(#datetime(2020, 4, 24, 0, 0, 0))},
                each Date.StartOfMonth(Date.From(_))
            )
        ),
        each Date.MonthName(_)
    ),

    but indeed I need to add the year number, where can I add the corresponding year in the formula? 

    Many thanks

    Monday, February 25, 2019 12:46 PM
  • I copypasted a portion of my script that should be useful

        mindate= List.Min(extractDate[SourceFileDate]),
        maxdate= List.Max(extractDate[SourceFileDate]),
        yearcol = List.Numbers(Number.FromText(Text.Start(mindate,4))
    ,Number.FromText(Text.Start(maxdate,4))+1-Number.FromText(Text.Start(mindate,4))),
        #"Converted to Table" = Table.FromList(yearcol, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Added Custom" = Table.AddColumn(#"Converted to Table", "months", each {"01","02","03","04","05","06","07","08","09","10","11","12"}),
        #"Expanded months" = Table.ExpandListColumn(#"Added Custom", "months"),
        #"Inserted Merged Column" = Table.AddColumn(#"Expanded months", "periodSort", each Text.Combine({Text.From([Column1], "en-US"), [months]}, ""), type text),
        #"Filtered Rows1" = Table.SelectRows(#"Inserted Merged Column", each ([periodSort] >= mindate and [periodSort] < maxdate )),
        #"Added Custom1" = Table.AddColumn(#"Filtered Rows1", "period", each Text.From([Column1])&" "&Text.Start(Date.MonthName(#date(2000,Number.FromText([months]),01)),3)),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Column1", "months"})

    With this, I create a field with format YYYYMM (201812) and one with format YYYY MMM (2018 dec)

    Depending on how you want it, you should change the script but the idea is the same

    • Marked as answer by Michiel Soede Monday, February 25, 2019 3:07 PM
    Monday, February 25, 2019 1:16 PM
  • You can easily use Date.ToText to personalize the date you want to see:

    = List.Transform(
        List.Distinct(
            List.Transform(
                {Number.From(#datetime(2019, 1, 4, 0, 0, 0))..Number.From(#datetime(2020, 4, 24, 0, 0, 0))}, 
                each Date.StartOfMonth(Date.From(_)) 
            ) 
        ),
        each Date.ToText(_, "MMMM yyyy")
    )

    • Edited by anthony34 Monday, February 25, 2019 2:37 PM
    • Marked as answer by Michiel Soede Monday, February 25, 2019 6:51 PM
    Monday, February 25, 2019 2:13 PM
  • Hi, 

    thanks for your reaction. One question though how does your extractDate[SourceFileDate] column looks like? If i make numbers from my date fields the code does not work where the following part requires text:

    yearcol = List.Numbers(Number.FromText(Text.Start(mindate,4))
    ,Number.FromText(Text.Start(maxdate,4))+1-Number.FromText(Text.Start(mindate,4))),

    and if I make text from it does work but the list is created however I end up with a list that starts for example with 

    3484 Jan

    3484 Feb

    etc

    Thanks in advance

    

    Monday, February 25, 2019 5:48 PM
  • great anthony, works like a charm! Many thanks
    Monday, February 25, 2019 6:51 PM
  • Ah yes, my SourceFileDate field is on the form YYYYMM like 201610 with the type "any". 

    Text.Start(maxdate,4) takes the first 4 characters of the field, which would be 2016. Using Text.End(maxdate,2) you can also get the last two characters, which would be 10 month. These functions need type "text"  


    • Marked as answer by Michiel Soede Sunday, March 3, 2019 2:25 PM
    Tuesday, February 26, 2019 8:22 AM