Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DateTime Calendar Template

Hi.

 

I just wanted to share this, it might be helpful to create a DateTime Calendar.

 

Feel free to remove the junk columns I added.

 

Cheers!

 

let
	StartDateTime = #datetime(2010,1,1,0,0,0),
	EndDateTime = #datetime(Date.Year(DateTime.LocalNow())+1,12,31,23,0,0),
	NumberOfHours = 24 * Duration.Days(Duration.From(EndDateTime-StartDateTime)) + 24,
	Source = List.DateTimes(StartDateTime,NumberOfHours ,#duration(0,1,0,0)),
	TableFromList = Table.FromList(Source,Splitter.SplitByNothing(),{"DateTime"}),
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"DateTime", type datetime}}),
	#"Added Index" = Table.AddIndexColumn(ChangedType, "Index", 1, 1),
    #"Inserted Date" = Table.AddColumn(#"Added Index", "Date", each Date.From([DateTime]), type date),
    #"Inserted Time" = Table.AddColumn(#"Inserted Date", "Time", each Time.From([DateTime]), type time),
    #"Inserted Day" = Table.AddColumn(#"Inserted Time", "Day", each Date.Day([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.ToText([Date], "ddd"), type text),
    #"Inserted Month" = Table.AddColumn(#"Inserted Day Name", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.ToText([Date], "MMM"), type text),
    #"Inserted Start of Month" = Table.AddColumn(#"Inserted Month Name", "StartofMonth", each Date.StartOfMonth([Date]), type date),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Start of Month", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Quarter", "Quarter Text", each "Q" & Text.From([Quarter])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Quarter Year", each [Quarter Text] & Text.From(Date.Year([Date]))),
    #"Inserted Year" = Table.AddColumn(#"Added Custom1", "Year", each Date.Year([Date]), Int64.Type),
    #"Added Custom2" = Table.AddColumn(#"Inserted Year", "YearMonth", each Text.From(Date.Year([Date])) & Text.PadStart(Text.From([Month]), 2, "0")),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom2",{{"Quarter Year", type text}, {"Quarter Text", type text}, {"Date", type date}, {"YearMonth", Int64.Type}, {"Index", Int64.Type}, {"Time", type time}})
in
    #"Changed Type"
  • Anonymous ,

     

    Actually it will be very useful some time. Thank you for the code. 

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support

    Anonymous ,

     

    Actually it will be very useful some time. Thank you for the code. 

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.