Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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.
User | Count |
---|---|
98 | |
91 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |