Forum Discussion
Modify table tricks needed
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
Community ChampionIs 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,
J
Hei, 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...
- amitchandak
Super User
tex628 , Please help. Anonymous , share error.
dont worry. let me try it first... i think i have a clue... the computer setting, yours is ; and mine is ,
