Forum Discussion
Overnight stops duration calculation
- Anonymous2 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.
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.