none
Can I make Date Dimension more simple ? RRS feed

  • Question

  • Can I make Date Dimension more simple ?

    I want to make Date Dimension Table.

    // Table
    [date]
    2001/1/1
    2001/1/2
    ...
    2010/12/31

    // M, I write
    let
      dateStart = #date(2001, 1, 1),
      dateEnd = #date(2010,12,31),
      s0 = { 0.. Duration.Days(dateEnd - dateStart)},
      s1= Table.FromList(s0, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
      s2= Table.RenameColumns(s1,{{"Column1", "id"}}),
      s3= Table.AddColumn(s2, "date", each dateStart + #duration([id],0,0, 0) ),
      s4= Table.TransformColumnTypes(s3,{{"date", type date}}),
      s5= Table.RemoveColumns(s4,{"id"})
    in
      s5

    Regards,
    Yoshihiro Kawabata

    Wednesday, October 2, 2013 12:29 AM

Answers

  • I found more simple way, by List.Generate

    let
        source = List.Generate ( () => #date(2001,1,1), each _ <= #date(2010,12,31), each _ + #duration(1,0,0,0))
    in
        source

    Thank,
    Yoshihiro Kawabata

    Friday, October 4, 2013 1:42 AM