Calendar table including time (other than 00:00)

[AlB]

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   

[v-juanli-msft · Accepted solution]

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

[AlB]

@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.