Forum Discussion

Petter120's avatar
Petter120
Helper I
6 years ago
Solved

Calculate time diffrence between two rows based on criterias

Hi!

I need help with to calculate the time diffrence between two rows based on multiple criterias im not sure if this will be done easier in Dax.

 

This is how the data looks in my table:

 

This is what i want to for the Area and the drift i want to calculate the diffrence between each row like this 

 

 

Thanks in advanced

Petter

 

 

 

Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Helle Petter120 

     

    yes, we are almost there..... hoping that my function is working properly šŸ™‚

    your query should look something like this

     

    let
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYRM9QyM9IwNDSwUDCysDA6VYHVyShqY4JS1hkk5ACQtcxmKXNMItaWlljCZpCpc0NABLxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Area = _t, Drift = _t, Date_Time = _t]),
    changedtype = Table.TransformColumnTypes(Quelle,{{"Area", type text}, {"Drift", Int64.Type}, {"Date_Time", type datetime}}),
    FinalTable = fnConvertTable(changedtype)

in
    FinalTable

     

    where my function is the last variable that is then passed as result with the in-statement. The part above the FInalTable-variable should be your old query...

     

    If this post helps or solves your problem, please mark it as solution.
    Kudos are nice to - thanks
    Have fun

    Jimmy

6 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello Petter120 ,

     

    i did try to do some grouping and based on the grouping applying a custom function that calculates the difference between each line. Seems to work really well. So give it a try and let me know if this can suite your requirement

    let
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYRM9QyM9IwNDSwUDCysDA6VYHVyShqY4JS1hkk5ACQtcxmKXNMItaWlljCZpCpc0NABLxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Area = _t, Drift = _t, Date_Time = _t]),
    #"GeƤnderter Typ" = Table.TransformColumnTypes(Quelle,{{"Area", type text}, {"Drift", Int64.Type}, {"Date_Time", type datetime}}),
    #"Gruppierte Zeilen" = Table.Group(#"GeƤnderter Typ", {"Area", "Drift"}, {{"AllRows", each _, type table [Area=text, Drift=number, Date_Time=datetime]}}),
    CreateDifferenceRowByRow = (tTable as table) => List.Generate
    (
        ()=>
        [
            Duration = #duration(0,0,0,0),
            Counter = 1
        ],
        each [Counter]<= Table.RowCount(tTable),
        (oldRecord)=> 
        [
            Duration = tTable[Date_Time]{oldRecord[Counter]}-tTable[Date_Time]{oldRecord[Counter]-1},
            Counter = oldRecord[Counter]+1
        ],
        each [Duration]
    ),

    AddDuration = Table.AddColumn
    (
        #"Gruppierte Zeilen", 
        "Duration", 
        each CreateDifferenceRowByRow([AllRows])
    ),
    AddCombine = Table.AddColumn
    (
        AddDuration, 
        "CombineAllRowsWIthDuration", 
        each Table.FromColumns
        (
            Table.ToColumns
            (
                [AllRows]
            )&{[Duration]},
            Table.ColumnNames
            (
                [AllRows]
            )&{"Duration"}
        )
    ),
    DeleteNotNeededColumns = Table.RemoveColumns
    (
        AddCombine,
        {"AllRows", "Duration"}
    ),
    Expand = Table.ExpandTableColumn
    (
        DeleteNotNeededColumns, 
        "CombineAllRowsWIthDuration", 
        {"Date_Time", "Duration"}, {"Date_Time", "Duration"}
    ),
    AdaptType = Table.TransformColumnTypes
    (
        Expand,
        {{"Date_Time", type datetime}, {"Duration", type duration}}
    )
in
    AdaptType

     

    If this post helps or solves your problem, please mark it as solution.
    Kudos are nice to - thanks
    Have fun

    Jimmy

    • Petter120's avatar
      Petter120
      Helper I
      6 years ago

      Hi Jimmy801,

      I cant some kind of error and i think it just has something to do with the date format. 

       

      When i import the table into Power Query the date format in the column "Date_Time" is as we use it in sweden YYYY-MM-DD HH:MM:SS like this:

       

      But when i run your code i get an error like this for the "Date_Time":

       

       

       

       

       

      And when i check the error i get this information and it says in english "DateFormat:Error. The indata cant be parsed as given for a DateTime-Value 
      Information: 14.12.2019 08:00

       

       

       

       

      So i guess i need to convert the DD. MM.YYY HH:MM to YYYY-MM-DD HH:MM:SS ?

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        6 years ago

        Hello Petter120 ,

         

        see the problem. Then let'ts try like this.

        Create a new blank query, past this code and name the function to "fnConvertTable"

        then go to your query and write this code

        FinalTable = fnConvertTable(VariableOfYourLastStep)

        in

        FinalTable

         

        then it should work

        (tTable as table) => 
let

    #"Gruppierte Zeilen" = Table.Group(tTable, {"Area", "Drift"}, {{"AllRows", each _, type table [Area=text, Drift=number, Date_Time=datetime]}}),
    CreateDifferenceRowByRow = (tTable as table) => List.Generate
    (
        ()=>
        [
            Duration = #duration(0,0,0,0),
            Counter = 1
        ],
        each [Counter]<= Table.RowCount(tTable),
        (oldRecord)=> 
        [
            Duration = tTable[Date_Time]{oldRecord[Counter]}-tTable[Date_Time]{oldRecord[Counter]-1},
            Counter = oldRecord[Counter]+1
        ],
        each [Duration]
    ),

    AddDuration = Table.AddColumn
    (
        #"Gruppierte Zeilen", 
        "Duration", 
        each CreateDifferenceRowByRow([AllRows])
    ),
    AddCombine = Table.AddColumn
    (
        AddDuration, 
        "CombineAllRowsWIthDuration", 
        each Table.FromColumns
        (
            Table.ToColumns
            (
                [AllRows]
            )&{[Duration]},
            Table.ColumnNames
            (
                [AllRows]
            )&{"Duration"}
        )
    ),
    DeleteNotNeededColumns = Table.RemoveColumns
    (
        AddCombine,
        {"AllRows", "Duration"}
    ),
    Expand = Table.ExpandTableColumn
    (
        DeleteNotNeededColumns, 
        "CombineAllRowsWIthDuration", 
        {"Date_Time", "Duration"}, {"Date_Time", "Duration"}
    ),
    AdaptType = Table.TransformColumnTypes
    (
        Expand,
        {{"Date_Time", type datetime}, {"Duration", type duration}}
    )
in
    AdaptType

         

        If this post helps or solves your problem, please mark it as solution.
        Kudos are nice to - thanks
        Have fun

        Jimmy