Forum Discussion
Anonymous
7 years agoNot applicable
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-msftCommunity 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.