The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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"
Solved! Go to Solution.
@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.
@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.
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
158 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |