Forum Discussion

kmilarov's avatar
kmilarov
Helper II
2 years ago
Solved

Overnight stops duration calculation

Hi Guys, I have a list of cars (C,D,E,F,....)  arriving for nightstops. Som eof them arrive before midnight, some of them arrive after midnight (which is nigthstop day+1). But all of them depart / re...
  • Anonymous's avatar
    Anonymous
    2 years ago

    You have to load the data into power query and next create a custom column that calculates the Nightstop Day for each row and refer this M code:(let
    Source = YourSourceData, // Replace with your actual source
    #"Added Custom" = Table.AddColumn(Source, "Nightstop Day", each Date.From([Real arrival date/time]))
    in
    #"Added Custom")     This columneffectively giving you the Nightstop Day.

    --Next bring the "Real departure" for the next day into the current row for the same car. To do this, you can merge the table with itself based on the Nightstop Day. 
    Then Duplicate your table in Power Query to create two copies after that Rename the second copy to "Departures." and Create a custom column in the "Arrivals" table to add 1 day to the Nightstop Day.
    Refer this M code:(let
    Source = YourSourceData, // Replace with your actual source
    #"Added Custom" = Table.AddColumn(Source, "Next Day", each [Nightstop Day] + #duration(1, 0, 0, 0))
    in
    #"Added Custom")   Now, merge the "Departures" table with the "Arrivals" table based on the "Car" and "Next Day" columns.

    --Create a custom column in the merged table to calculate the duration between "Real arrival date/time" and "Real departure" for each row, and refer this M code:(let
    MergedTable = ... // Your merged table here
    #"Added Custom" = Table.AddColumn(MergedTable, "Duration", each Duration.From([Real departure] - [Real arrival date/time]))
    in
    #"Added Custom") and then Load the final merged and transformed data into Power BI.
    --create the visuals you need. You can use the "Duration" column you calculated in Power Query to display the duration in hours/minutes, this will allow you to calculate and display the Nightstop Day, arrival time, departure time, and the duration the car was out of service in Power BI.