Calculate time diffrence between two rows based on criterias

[Petter120]

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

[Jimmy801]

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]

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]

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

[Petter120]

Ok @Jimmy801  i thinking im almost there, just clarify a few  things:

After the import of "Tabell1" i go and create a blank query and add the code.

Then it ask me to add a parameter where i chose "Tabell1"

Then i rename the function to "fnConvertTable"

Then "Tabell1" looks like this

When i open the advanced Editor it looks like this:

Then i pass the new code

And i its here im guessing om doing something wrong?

Because i get this error: Expression.SyntaxError. Token Eof  expected

[Jimmy801 · Accepted solution]

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

[Petter120]

Thank you @Jimmy801 now it works 🙂