cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User III
Super User III

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   

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

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

2 REPLIES 2
Community Support
Community Support

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

Super User III
Super User III

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.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors