calcul time between 2 stations

[Milozebre]

I have a question.
I would like the duration between 2 stations.
I don't have an arrival time column.
I see a dateiff between line.
Example for train 121501 06:12 this is the departure so it will be 0
06:24 would be 12 ...

But per day I have several trains and then how to take into account the order which must be ASC by train.

I found a link but there is a column with the date of arrival which I do not have.
https://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339...
File filtered ASC for 1 trainFile without transformation

thank you

[Greg_Deckler]

If you could post data as text could probably figure this out. If you are just trying to calculate the time between different rows by train should be able to do that in a column using a similar technique to MTBF. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Milozebre]

Here's what you asked me for a file with sample data.
If I post this message as well as the link with the explanations it is that I didn't manage to do it because I don't have the same configuration.
I don't have an arrival column.
Because I have to calculate it on the arrival time at the next station and as you can see, it is not the next line.
I make efforts and I search by myself (the proof the link).

Date	Ngare	order	train	Theo
07-oct-17	2	9	121530	17:50:50
08-oct-17	7	4	121522	09:27:50
07-oct-17	9	2	121540	20:17:20
07-oct-17	9	2	121526	14:17:20
08-oct-17	4	7	121522	09:39:50
07-oct-17	1	10	121504	06:32:20
07-oct-17	8	8	121507	08:52:10
07-oct-17	1	1	121509	10:12:00
07-oct-17	6	6	121507	08:41:30
07-oct-17	1	1	121513	13:12:00
08-oct-17	1	10	121534	19:02:20
07-oct-17	4	4	121513	13:35:00
07-oct-17	4	4	121529	19:35:00
08-oct-17	5	6	121524	11:36:40
08-oct-17	1	10	121528	17:02:20
08-oct-17	1	10	121522	10:02:20
07-oct-17	2	9	121522	09:50:50
07-oct-17	8	3	121536	19:22:50
07-oct-17	9	2	121522	09:17:20
08-oct-17	9	9	121509	10:57:30
07-oct-17	9	9	121529	19:57:30
07-oct-17	1	1	121541	22:42:00
08-oct-17	8	8	121539	22:27:10
07-oct-17	6	6	121503	07:11:30
07-oct-17	2	9	121534	18:50:50
07-oct-17	8	8	121537	21:52:10
07-oct-17	4	4	121541	23:10:10
07-oct-17	2	2	121541	22:59:00
07-oct-17	3	3	121515	16:32:00
07-oct-17	4	7	121540	20:39:50
07-oct-17	7	7	121503	07:17:10
07-oct-17	5	6	121524	11:36:40
08-oct-17	5	6	121510	07:06:40
07-oct-17	10	1	121510	06:42:00
07-oct-17	7	4	121524	11:27:50
08-oct-17	6	5	121510	07:03:20
07-oct-17	1	10	121530	18:02:20
07-oct-17	1	10	121540	21:02:20
07-oct-17	8	3	121518	08:07:40
08-oct-17	3	8	121540	20:43:00
07-oct-17	3	8	121536	19:43:00
07-oct-17	6	6	121511	12:44:10
08-oct-17	3	3	121509	10:32:00
07-oct-17	2	2	121515	16:24:00
08-oct-17	6	6	121507	08:41:30
08-oct-17	9	2	121528	16:17:20
08-oct-17	9	9	121507	08:57:30
08-oct-17	2	9	121510	07:20:40
08-oct-17	9	9	121511	13:00:10

[FrankAT · Accepted solution]

Hi @Milozebre ,

hope this may help:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVVbbsMwDLtLvjtAluz4cZWiXzvAfnZ/zPIjZTo5LTADwUBTFkmp9/tG8evn+/fLxe22cT25HscuCOlHLIHq3/a4VWR6IvX4gWS9RrlwPJDAmQevIr1yMpVKy9dI3vXDIxKq+/ECrC7Zqu700ICS3qO9CFvl0zgNqf+gVAIXtyCdyKZXbYkL/Ufu4yCnd0WuOZ3ohyBnWrQkzYRcyGzJg0uTVIL1UERy7pyAhPIBWuJ2pfazF//moZx6nOChSyh3Se2eMKPTe8joi6Eyddp7T8zvMjo57eRlqD6tD9EyFJFTURuJ1nv9qI/0pvUYUckdWafOiOgpeCoCVZgZvNPMNz/ThZ5H9XbVLQYE09Q7qmEmC8m4HUbvIVsJFXDTaQhdG+RFlud2mBvH3g4RkIdKpp6fpx6RLdKVk55I9J1w5qnvJm+2dFq3ozys23Q2PryWF2uQznuEuvP2zJ2gXVK3gOLQudQ3Xn2CIZRAnKZNXlbWH8Ebg2wjMfauS1u8PwxNdpzmINtxwojO4LG3xvPNtk+LhZM65wcLZ/4qRYsTB3k6r5Jec3aVVMym0uMP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Ngare = _t, order = _t, train = _t, Theo = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Ngare", Int64.Type}, {"order", Int64.Type}, {"train", Int64.Type}, {"Theo", type time}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"train", Order.Ascending}, {"Theo", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"train"}, {{"Count", each _, type table [Date=date, Ngare=number, order=number, train=number, Theo=time]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fxFunction([Count])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Date", "Ngare", "order", "train", "Theo", "Index", "Index.1", "Theo.1", "Custom"}, {"Date", "Ngare", "order", "train", "Theo", "Index", "Index.1", "Theo.1", "Custom.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Index", "Index.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "Difference"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Ngare", Int64.Type}, {"order", Int64.Type}, {"train", Int64.Type}, {"Theo", type time}, {"Theo.1", type time}, {"Difference", type duration}})
in
    #"Changed Type1"

// fxFunction
(tabelle as table) =>
let
    #"Added Index" = Table.AddIndexColumn(tabelle, "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Theo"}, {"Theo.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "Custom", each [Theo] - [Theo.1])
in
    #"Added Custom"

Regards FrankAT

[Milozebre]

hi @FrankAT 

Its amazing... 

I try with my file but the result is not correct. 

Here is my file : 

Here is my file : https://www.transfernow.net/z75cwL042020

Thank you in advance