How do I add hours/minutes/seconds to my Date Tabel, to use in Direct Query?

[Anonymous]

Hey everyone! 

I've made a Query to generate a Date Table to use in Direct Query reports. It works perfectly fine, but my company is requesting that I add hours to the Date Table. I'm quite new to Power BI, so my skills with DAX are not advanced. 
I'm not interested in making a Time Table too. I want it all to be one big DateTime Tabel. The reason is, that the query is supposed to be used in all the reports in our department, and it should be easy for my coworkers to do so. So if possible I just want to change my current query to add hours. Can someone out there rewrite it, so hours are added to each date?
The following is my current query:
 

let

//Variables

StartDate = #date(2020,1,1),

EndDate = Date.EndOfYear(Today),

Duration = Duration.Days(Duration.From(EndDate-StartDate))+1,

Today = DateTime.Date(DateTime.LocalNow()),

//Date Columns

Dates = List.Dates(StartDate, Duration, #duration(1,0,0,0)),

#"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),

#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),

#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),

#"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "QuarterNo", each Date.QuarterOfYear([Date]), Int64.Type),

#"Added Custom" = Table.AddColumn(#"Inserted Quarter", "Quarter", each "K"&Text.From([QuarterNo])),

#"Inserted Month" = Table.AddColumn(#"Added Custom", "MonthNo", each Date.Month([Date]), Int64.Type),

#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Text.Start(Date.MonthName([Date]),3), type text),

#"Replaced Value" = Table.ReplaceValue(#"Inserted Month Name","May","Maj",Replacer.ReplaceText,{"Month Name"}),

#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Oct","Okt",Replacer.ReplaceText,{"Month Name"}),

#"Inserted Week of Year" = Table.AddColumn(#"Replaced Value1", "WeekNo", each Date.WeekOfYear([Date]), Int64.Type),

#"Inserted Day" = Table.AddColumn(#"Inserted Week of Year", "Day", each Date.Day([Date]), Int64.Type),

#"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Text.Start(Date.DayOfWeekName([Date]),3), type text),

#"Renamed Columns1" = Table.RenameColumns(#"Inserted Day Name",{{"Day", "DayNo"}, {"Day Name", "Day Name"}}),

#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Day Name"}),

#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"DayNo", "Day"}, {"Month Name", "Month"}, {"WeekNo", "Week"}})

in

#"Renamed Columns2"

Thanks!

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

According to your description, you want to add an "hour" increasing  in your table and change the "date" to the "date/time". 

I update the M code you provided, you can try to use this:

let

//Variables

StartDate = #datetime(2020, 1, 1, 0, 0, 0),

EndDate = DateTime.From(Date.From(DateTime.LocalNow())),

Duration = Duration.TotalHours( Duration.From(EndDate-StartDate))+1,

Today = DateTime.Date(DateTime.LocalNow()),

 

//Date Columns

Dates = List.DateTimes(StartDate, Duration, #duration(0,1,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),

#"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]), Int64.Type),

#"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "QuarterNo", each Date.QuarterOfYear([Date]), Int64.Type),

#"Added Custom" = Table.AddColumn(#"Inserted Quarter", "Quarter", each "K"&Text.From([QuarterNo])),

#"Inserted Month" = Table.AddColumn(#"Added Custom", "MonthNo", each Date.Month([Date]), Int64.Type),

#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Text.Start(Date.MonthName([Date]),3), type text),

#"Replaced Value" = Table.ReplaceValue(#"Inserted Month Name","May","Maj",Replacer.ReplaceText,{"Month Name"}),

#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Oct","Okt",Replacer.ReplaceText,{"Month Name"}),

#"Inserted Week of Year" = Table.AddColumn(#"Replaced Value1", "WeekNo", each Date.WeekOfYear([Date]), Int64.Type),

#"Inserted Day" = Table.AddColumn(#"Inserted Week of Year", "Day", each Date.Day([Date]), Int64.Type),

#"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Text.Start(Date.DayOfWeekName([Date]),3), type text),

#"Renamed Columns1" = Table.RenameColumns(#"Inserted Day Name",{{"Day", "DayNo"}, {"Day Name", "Day Name"}}),

#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Day Name"}),

#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"DayNo", "Day"}, {"Month Name", "Month"}, {"WeekNo", "Week"}})

 

in

#"Renamed Columns2"

Then we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Anonymous]

Thanks for the solution, Aniya! 🙂 

I have a question. How come I can't "Mark as date table"? 

Will there be any disadvantages or big differences, when not marking it as a date table? 

Kind Regards, Ann