Forum Discussion

ahadi063's avatar
ahadi063
New Member
8 years ago

Need Help

Dear community! 

 

I need your kind help with something. I need to create a date column and associate hour column for a calendar look up. Is there a quick way to do this? For example, this is what I need:

 

Date:

11/01/2016 12:00 AM

11/01/2016 01:00 AM

11/01/2016 02:00 AM

11/01/2016 03:00 AM

....

Thank you for your help!!

Need Help

1 Reply

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    8 years ago

    It's not clear if you are looking for a DAX or Power Query solution.

     

    The query below generates a table with 1000 rows with datetimes, starting November 1, 2016 (it's not clear if your examples are November 1 or January 11), next I used Split Column to split on the left most space. I adjusted the generated code with regard to the column names.

     

    let
    Source = #table(type table[Datetime = datetime],List.Zip({List.DateTimes(#datetime(2016,11,1,0,0,0),1000,#duration(0,1,0,0))})),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(Source, {{"Datetime", type text}}, "en-US"), "Datetime", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Date", "Time"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date", type date}, {"Time", type time}})
in
    #"Changed Type"