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!
Hi 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!