Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |