Forum Discussion
Modify table tricks needed
Hei,
I need some help with this table. For each Trip, I need to define its start and end time by:
Lets take a unique Trip number and when the second column is Loading, take the Depart time as the start of a trip and write it into a new column, when the second column is Discharing, take the Arrive time as the end of a trip and write it into a new column. If there are several rows of Loading for one Trip number, take the first of all Depart time and If there are several rows of Discharing for one Trip number, take the last of all Arrive time. I wish to have a table with only three columns, Trip number, Start time and end time of this trip. Can someone help me with this? Thanks in advance.
| Trip number | Reason for call | Depart | Arrive |
| 01901 | Loading | 1/23/2019 13:00 | 1/20/2019 11:10 |
| 01901 | Discharging | 2/10/2019 13:30 | 2/5/2019 0:01 |
| 01902 | Loading | ||
| 01902 | Loading | 5/4/2019 1:00 | 4/23/2019 16:00 |
| 01902 | Discharging | 5/15/2019 16:48 | 5/13/2019 8:36 |
| 01903 | Loading | 6/9/2019 8:06 | 6/5/2019 11:30 |
| 01903 | Discharging | ||
| 01903 | Discharging | 8/3/2019 8:34 | 8/2/2019 2:12 |
| 01904 | Loading | 8/22/2019 22:30 | 8/19/2019 3:36 |
| 01904 | Loading | 8/14/2019 16:35 | 8/12/2019 7:58 |
| 01904 | Loading | ||
| 01904 | Discharging | 10/7/2019 16:48 | 9/27/2019 21:00 |
| 01904 | Discharging | ||
| 01905 | Loading | ||
| 01905 | Loading | 12/20/2019 11:00 | 12/16/2019 17:30 |
| 01905 | Discharging | 1/4/2020 9:00 | 1/2/2020 1:00 |
| 02001 | Loading | 1/12/2020 12:24 | 1/9/2020 19:30 |
| 02001 | Loading | 1/19/2020 21:00 | 1/18/2020 10:10 |
| 02001 | Discharging | 2/5/2020 17:00 | 2/1/2020 16:30 |
| 02001 | Discharging | 2/10/2020 12:12 | 2/6/2020 9:30 |
| 02001 | Discharging | 3/1/2020 3:00 | 2/11/2020 19:30 |
| 01901 | Loading | 8/23/2019 7:30 | 8/18/2019 18:06 |
| 01901 | Discharging | 9/8/2019 17:30 | 9/6/2019 8:06 |
| 01902 | Loading | 9/14/2019 12:48 | 9/8/2019 17:30 |
| 01902 | Discharging | 10/15/2019 9:12 | 10/4/2019 16:48 |
| 01903 | Loading | ||
| 01903 | Loading | 10/27/2019 1:30 | 10/21/2019 12:00 |
| 01903 | Discharging | 11/9/2019 21:42 | 11/5/2019 21:30 |
| 01904 | Loading | 12/4/2019 19:30 | 11/29/2019 7:30 |
| 01904 | Discharging | 12/19/2019 7:00 | 12/14/2019 16:00 |
| 01905 | Loading | 1/5/2020 20:28 | 1/2/2020 10:00 |
| 01905 | Discharging | ||
| 01905 | Loading | 1/10/2020 10:00 | 1/6/2020 20:00 |
| 01905 | Discharging | 2/25/2020 13:48 | 2/9/2020 21:00 |
| 01905 | Discharging | 3/4/2020 16:00 | 2/27/2020 14:42 |
Alright, give this a try:
I begin with this table:
Create a duplicate of this table, now we have Table 1 & Table 2.
In Table 1, filter reason for call on "Loading":
In Table 1, highlight [Trip number] and [Unit Number] then press "Group By". Use the same setting as the picture below:
Now we move onto Table 2. Filter the table on discharging then do anoth "Group By" with these settings:
Finally we want to merge the two tables. Press "Merge as New" then use the following settings (Ctrl click to highlight more columns):
Finally expand the "End" column:
Should give you the following result, which i hope is correct:
Br,
JHappy to hear! I can actually help you out with that 😉
Br,
J
21 Replies
Is this what you're looking for?
If that's the case i used these two calculated columns:Start = CALCULATE( MIN('Table (2)'[Depart]) ; ALL('Table (2)') ; 'Table (2)'[Reason for call] = "Loading" ; 'Table (2)'[Trip number] = EARLIER('Table (2)'[Trip number]))End = CALCULATE( MAX('Table (2)'[Arrive]) ; ALL('Table (2)') ; 'Table (2)'[Reason for call] = "Discharging" ; 'Table (2)'[Trip number] = EARLIER('Table (2)'[Trip number]))
Br,
JHei, yes, this is almost what i want. I just need one row for a unique Trip number and remove the three columns in the middle...
However, you syntax does not work on my pc. I changed my table name to Table (2), as I guess that is your table name and in Data - > Modelling -> Add a new column and put in your line into the formula area... but i got error message... is it becuase my dates have hierachy? but I cant remove it...
tex628 , Please help. Anonymous , share error.
Anonymous
Try a new table like
summarize(table, table[Trip number],"Depart",firstnonblank(Table[Depart],blank()),"Arrive",lastnonblank(Table[Arrive],blank())) or summarize(table, table[Trip number],"Depart",min(Table[Depart]),"Arrive",max(Table[Arrive]))
