Forum Discussion

Milozebre's avatar
Milozebre
Icon for Helper V rankHelper V
6 years ago
Solved

calcul time between 2 stations

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/339586
File filtered ASC for 1 trainFile without transformation

 

 

 

 

 

 

 

thank you

 

Need Help
  • FrankAT's avatar
    FrankAT
    6 years ago

    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

4 Replies

    • Milozebre's avatar
      Milozebre
      Icon for Helper V rankHelper V
      6 years ago

      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).

      DateNgareordertrainTheo
      07-oct-172912153017:50:50
      08-oct-177412152209:27:50
      07-oct-179212154020:17:20
      07-oct-179212152614:17:20
      08-oct-174712152209:39:50
      07-oct-1711012150406:32:20
      07-oct-178812150708:52:10
      07-oct-171112150910:12:00
      07-oct-176612150708:41:30
      07-oct-171112151313:12:00
      08-oct-1711012153419:02:20
      07-oct-174412151313:35:00
      07-oct-174412152919:35:00
      08-oct-175612152411:36:40
      08-oct-1711012152817:02:20
      08-oct-1711012152210:02:20
      07-oct-172912152209:50:50
      07-oct-178312153619:22:50
      07-oct-179212152209:17:20
      08-oct-179912150910:57:30
      07-oct-179912152919:57:30
      07-oct-171112154122:42:00
      08-oct-178812153922:27:10
      07-oct-176612150307:11:30
      07-oct-172912153418:50:50
      07-oct-178812153721:52:10
      07-oct-174412154123:10:10
      07-oct-172212154122:59:00
      07-oct-173312151516:32:00
      07-oct-174712154020:39:50
      07-oct-177712150307:17:10
      07-oct-175612152411:36:40
      08-oct-175612151007:06:40
      07-oct-1710112151006:42:00
      07-oct-177412152411:27:50
      08-oct-176512151007:03:20
      07-oct-1711012153018:02:20
      07-oct-1711012154021:02:20
      07-oct-178312151808:07:40
      08-oct-173812154020:43:00
      07-oct-173812153619:43:00
      07-oct-176612151112:44:10
      08-oct-173312150910:32:00
      07-oct-172212151516:24:00
      08-oct-176612150708:41:30
      08-oct-179212152816:17:20
      08-oct-179912150708:57:30
      08-oct-172912151007:20:40
      08-oct-179912151113:00:10
      • FrankAT's avatar
        FrankAT
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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