Forum Discussion
Splitting duration between time frames
Hello v-piga-msft sorry for the delay, in the meanwhile I developed a solution.
What I meant was, for the first line the column trip duration = 18 minutes
And the trip started before 21h, so there were 15 minutes e that time frame and 3 minutes in the next time frame.The way I did it, I don't know if it's the simplest but was the one I could remember, was to create calculated columns.
First of all, on power query I extracted the hour of departure and arrival (2 columns) and grouped them in the time frames I mentioned (00:00-05:59 | 06:00-20:59 | 21:00-23:59).Then there were some calculated columns because I also had to divide between days (so for example, a trip starting today before midnight and ending tomorrow dawn was going to be divided in 2 time frames and in 2 days)
The code it's a bit complex and specific do some of the data conditions but I will still add a link for a word document and state this as the solution https://1drv.ms/w/s!AkP8HOhtvBzT4jgnTlVSlwxnonPf?e=T276py
Also, the information had to be in hours (not in minutes) so I had to divide minutes by 60 and seconds by 60^2.
There are 7 calculated columns, more or less one per page.
But thank you very much for your attention!
Hello v-piga-msft
Thank you for your reply, here goes the example. Let me know if you understand it.
I want to know how much of the trip is in one timeframe and how much of the trip is in another one.
| Bus Nº | Trip | Departure | Time Fram Departure | Arrival | Time frame arrival | Kms | Trip Duration | Duration in hours |
| 12 | 1 | 18/01/2017 20:45 | 06:00 - 20:59 | 18/01/2017 21:03 | 21:00 - 23:59 | 2.4 | 00:18:00 | 0.30 |
| 12 | 2 | 18/01/2017 20:51 | 06:00 - 20:59 | 18/01/2017 20:52 | 06:00 - 20:59 | 2.4 | 00:19:00 | 0.02 |
| Desired Visual Result (Matrix) in minutes of duration | ||||||||
| Trip | 06:00 - 20:59 | 21:00 - 23:59 | ||||||
| 1 | 15 | 3 | ||||||
| 2 | 19 | 0 |
Resident RockstarHi Anonymous ,
Sorry I still have confused about your desired output.
If it is convenient, could you explain the logic of your output. Why the value is 15 duting 06:00 - 20:59 for Trip 1?
Best Regards,
Cherry
Hello v-piga-msft sorry for the delay, in the meanwhile I developed a solution.
What I meant was, for the first line the column trip duration = 18 minutes
And the trip started before 21h, so there were 15 minutes e that time frame and 3 minutes in the next time frame.The way I did it, I don't know if it's the simplest but was the one I could remember, was to create calculated columns.
First of all, on power query I extracted the hour of departure and arrival (2 columns) and grouped them in the time frames I mentioned (00:00-05:59 | 06:00-20:59 | 21:00-23:59).Then there were some calculated columns because I also had to divide between days (so for example, a trip starting today before midnight and ending tomorrow dawn was going to be divided in 2 time frames and in 2 days)
The code it's a bit complex and specific do some of the data conditions but I will still add a link for a word document and state this as the solution https://1drv.ms/w/s!AkP8HOhtvBzT4jgnTlVSlwxnonPf?e=T276py
Also, the information had to be in hours (not in minutes) so I had to divide minutes by 60 and seconds by 60^2.
There are 7 calculated columns, more or less one per page.
But thank you very much for your attention!