cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ahadi063 Frequent Visitor
Frequent Visitor

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!!

1 REPLY 1
MarcelBeug Super Contributor
Super Contributor

Re: Need Help

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"

 

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors