Reply
AlB Super Contributor
Super Contributor
Posts: 1,189
Registered: ‎11-12-2018
Accepted Solution

Calendar table including time (other than 00:00)

Hi all,

 

When we want to slice data by date we create a Calendar table, which can be easily done with CALENDARAUTO or CALENDAR. This includes no time (well, only 00:00:00). Now, imagine we have data where time is actually relevant and we want to slice it in specific time slots. For instance we need to filter transactions between 13:15 and 15:45 on a specific day.

 

How would you go about that?

Is there an easy way, built in or otherwise, to create a date table that includes the time granularity needed?

 

Thanks a  lot   

 

 


Accepted Solutions
Community Support Team
Posts: 2,532
Registered: ‎03-15-2018

Re: Calendar table including time (other than 00:00)

Hi @AlB

Based on my test, you should be able to use the formula below to create a calendar table with date and time. eg 2018/1/1 00:00:00~2018/1/5 23:45:00

In Queries Editor

create a new blank query, open the advanced editor, paste the code below

let
    Source = #datetime(2018,1,5,23,59,59)-#datetime(2018,1,1,0,0,0),  //the duration between start and end date,you could change the start and end date 
    #"Calculated Total Minutes" = Duration.TotalMinutes(Source),      //calculate the total munites of the duration 
    final=List.DateTimes(#datetime(2018, 1, 1, 0, 0, 0), #"Calculated Total Minutes"/15, #duration(0, 0, 15, 0)),// incrementing by 15 minutes
    #"Converted to Table" = Table.FromList(final, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

Note: don't paste the blue character into the editor, it is just for explanation.

Reference:

https://docs.microsoft.com/en-us/powerquery-m/list-datetimes

https://docs.microsoft.com/en-us/powerquery-m/duration-totalminutes

 

Best Regards

Maggie

 

 

View solution in original post

Attachment

All Replies
Community Support Team
Posts: 2,532
Registered: ‎03-15-2018

Re: Calendar table including time (other than 00:00)

Hi @AlB

Based on my test, you should be able to use the formula below to create a calendar table with date and time. eg 2018/1/1 00:00:00~2018/1/5 23:45:00

In Queries Editor

create a new blank query, open the advanced editor, paste the code below

let
    Source = #datetime(2018,1,5,23,59,59)-#datetime(2018,1,1,0,0,0),  //the duration between start and end date,you could change the start and end date 
    #"Calculated Total Minutes" = Duration.TotalMinutes(Source),      //calculate the total munites of the duration 
    final=List.DateTimes(#datetime(2018, 1, 1, 0, 0, 0), #"Calculated Total Minutes"/15, #duration(0, 0, 15, 0)),// incrementing by 15 minutes
    #"Converted to Table" = Table.FromList(final, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

Note: don't paste the blue character into the editor, it is just for explanation.

Reference:

https://docs.microsoft.com/en-us/powerquery-m/list-datetimes

https://docs.microsoft.com/en-us/powerquery-m/duration-totalminutes

 

Best Regards

Maggie

 

 

Attachment
AlB Super Contributor
Super Contributor
Posts: 1,189
Registered: ‎11-12-2018

Re: Calendar table including time (other than 00:00)

@v-juanli-msft

That's a really cool, and quick, solution. Thanks very much Maggie.

Can you create this by only using items from the menus or did you have to add the M code manually, on the editor.