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,
J
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,
J
The reason that the two columns are not present is that they are calculated columns, they depend on the datamodel as it is when it's loaded and they cant actually exist before the load!
What we want to do instead is write a table statement, i'd say we want to do something like this:
New Table = SUMMARIZE( 'Table (2)' ; [Trip Number] ; "Start" ; MIN([Start]) ; "End" ; MAX([End]))
Let me know how it goes!
Br,
J
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,
J
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
A Year in Review - December 2024
Find out what content was popular in the Fabric community during 2024.
